写sql时经常会碰到条件不定的情况，好多兄弟都是在哪儿无穷的拼接字符串，我也这样做过，太难弄了，老是搞错，后来用了这样的技巧受用无限：
原来的方法
ALTER procEDURE [dbo].[HollySys_NewsArticles_GetLinks]( @sWhere nvarchar(255) )ASdeclare @strYuJu nvarchar(1000) set @strYuJu='SELECT [ID] ,[OrganizationID] ,[Link] ,[LinkTitle] ,[CreateDate] ,[CreateByUser] ,[IsDeleted] ,[ModafiedDate] ,[ModafiedUser] ,[Memo] FROM [dbo].[HollySys_OrgLink] where [IsDeleted]=0 ' set @[email protected][email protected] exec (@strYuJu)
其实最终就是学会OR的妙用
ALTER PROCEDURE [dbo].[HollySys_NewsArticles_GetArticles]( @ModuleId int, @CategoryID int, @SpecialID int, @PageSize int, @CurrentPageIndex int, @Passed bit, @Hot bit, @Elite bit, @Pic bit, @Categories varchar(1000), @SortField varchar(100), @SearchCriteria varchar(100), @SearchType tinyint, @CreatedByUser varchar(100), @Status tinyint, @StartDate datetime, @EndDate datetime, @TotalRecords int output, @GroupManage nvarchar(255)---扩展按部门查询)AS-- Set the page boundsDECLARE @PageLowerBound INTDECLARE @PageUpperBound INTIF @CurrentPageIndex = -1 SET @PageLowerBound = 0ELSE SET @PageLowerBound = @PageSize * @CurrentPageIndexSET @PageUpperBound = @PageSize - 1 + @PageLowerBound-- Create a temp table TO store the select resultsCREATE TABLE #PageIndexFor_HollySys_NewsArticles_Article( IndexId int IDENTITY (0, 1) NOT NULL, ArticleID int)IF @CurrentPageIndex = -1 AND @PageSize < 2147483647 SET ROWCOUNT @PageSizeINSERT INTO #PageIndexFor_HollySys_NewsArticles_Article (ArticleID)SELECT A.[ArticleID]FROM dbo.HollySys_NewsArticles_Article A INNER JOIN dbo.HollySys_NewsArticles_Category C ON A.CategoryID = C.CategoryIDWHERE C.ModuleId = @ModuleId AND (A.Passed = @Passed) AND A.Hot IN(@Hot,0) AND A.Elite IN(@Elite,0) AND ((A.DefaultPicUrl <> '' and @Pic =1)or @Pic=0) AND (CHARINDEX (','+CONVERT(varchar(10),C.CategoryID)+',',',[email protected]+',') > 0) AND ((A.Title LIKE '%' + @SearchCriteria + '%' AND (@SearchType = 1 OR @SearchType = 2)) OR ((A.Content LIKE '%' + @SearchCriteria + '%' OR A.Summary LIKE '%' + @SearchCriteria + '%' OR A.KeyWords LIKE '%' + @SearchCriteria + '%') AND (@SearchType = 1 OR @SearchType = 4)) OR (A.Author LIKE '%' + @SearchCriteria + '%' AND (@SearchType = 1 OR @SearchType = 3)) OR @SearchCriteria = '') AND (A.CreatedByUserID = @CreatedByUser OR @CreatedByUser = '-1') AND ( (@Status = 0) OR ((@Status = 1) AND ((A.StartDate <= GETDATE() OR A.StartDate IS NULL) AND (A.EndDate > GETDATE()-1 OR A.EndDate IS NULL))) OR ((@Status = 2) AND (A.StartDate > GETDATE()) AND (A.StartDate IS NOT NULL)) OR ((@Status = 3) AND (A.EndDate <= GETDATE()-1) AND (A.EndDate IS NOT NULL)) ) AND (@StartDate < A.CreatedDate OR @StartDate IS NULL) AND (A.CreatedDate < @EndDate + 1 OR @EndDate IS NULL) AND (A.ArticleID IN (SELECT ArticleID FROM [/color]dbo.HollySys_NewsArticles_SpecialArticle WHERE SpecialID = @SpecialID) OR @SpecialID=-1)ORDER BY