写sql时经常会遇到条件不定的情况,这样的技巧受用无限
写sql时经常会碰到条件不定的情况,这样的技巧受用无限
写sql时经常会碰到条件不定的情况,好多兄弟都是在哪儿无穷的拼接字符串,我也这样做过,太难弄了,老是搞错,后来用了这样的技巧受用无限:
原来的方法
其实最终就是学会OR的妙用
写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