写sql时经常会碰到条件不定的情况，这样的技巧受用无限

写sql时经常会碰到条件不定的情况，好多兄弟都是在哪儿无穷的拼接字符串，我也这样做过，太难弄了，老是搞错，后来用了这样的技巧受用无限：

原来的方法

ALTER procEDURE [dbo].[HollySys_NewsArticles_GetLinks]
(
	 
	@sWhere nvarchar(255) 
)

AS
declare @strYuJu  nvarchar(1000) 
set @strYuJu='SELECT [ID]
      ,[OrganizationID]
      ,[Link]
      ,[LinkTitle]
      ,[CreateDate]
      ,[CreateByUser]
      ,[IsDeleted]
      ,[ModafiedDate]
      ,[ModafiedUser]
      ,[Memo]
     
  FROM [dbo].[HollySys_OrgLink] 
  where  [IsDeleted]=0 '  
  set @strYuJu=@strYuJu+@sWhere
  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 bounds
DECLARE @PageLowerBound INT
DECLARE @PageUpperBound INT
IF @CurrentPageIndex = -1
	SET @PageLowerBound = 0
ELSE
	SET @PageLowerBound = @PageSize * @CurrentPageIndex
SET @PageUpperBound = @PageSize - 1 + @PageLowerBound

-- Create a temp table TO store the select results
CREATE TABLE #PageIndexFor_HollySys_NewsArticles_Article
(
     IndexId int IDENTITY (0, 1) NOT NULL,
     ArticleID int
)

IF @CurrentPageIndex = -1 AND @PageSize < 2147483647
	SET ROWCOUNT @PageSize

INSERT 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.CategoryID
WHERE 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)+',',','+@Categories+',') > 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