SQL动态拼接字符串生成分页存储过程
今天做一个订单查询的时候,要求动态条件查询订单的明细,这个在后台写程序其实也很简单,但是今天脑洞大开,就尝试一下直接在写一条存储过程在后台动态拼接字符串然后查询,不知道这样写会不会有什么其他的问题呢?
具体代码如下:
本人还是个菜鸟,语文水平也很低,描述的比较含糊不清,望各位大神指点指点
1 USE [#####] 2 GO 3 /****** Object: StoredProcedure [dbo].[MES_PDO_Page] Script Date: 01/05/2016 18:08:31 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 ALTER proc [dbo].[MES_PDO_Page] 9 @pageIndex INT, 10 @pageSize INT, 11 @Status INT, 12 @PDO VARCHAR(50), 13 @WorkCenter VARCHAR(50), 14 @pageCount INT OUTPUT 15 16 AS 17 BEGIN 18 DECLARE @conditions NVARCHAR(500) 19 DECLARE @sort NVARCHAR(50) 20 --动态组合查询条件 21 SET @conditions=' ' 22 SET @sort=' ' 23 IF (@Status>0) 24 --因为是组合字符串,所以必须把数值类型转化为字符串,否则报错 25 SET @conditions=@conditions+'AND (Status='+CAST(@Status AS NVARCHAR(10))+')' 26 SET @sort='PARTITION BY Status ' 27 IF (@PDO IS NOT NULL) 28 --'的转移字符是两个'' 29 SET @conditions=@conditions+'AND (PDO='''+@PDO+''')' 30 IF (@WorkCenter IS NOT NULL) 31 --SET @conditions+=@conditions+'AND (WorkCenter Like %'+@WorkCenter+'%)' 32 SET @conditions+=@conditions+'AND (WorkCenter='''+@WorkCenter+''')' 33 SET @sort='PARTITION BY WorkCenter ' 34 IF((@Status>0)AND(@WorkCenter IS NOT NULL)) 35 SET @sort='PARTITION BY WorkCenter,Status ' 36 --声明SQL语句字符串一定要长度要比字符串本身长度要长,不然会报错 37 DECLARE @sqlPage NVARCHAR(2000) 38 DECLARE @min INT, @max INT 39 SET @min=@pageSize*(@pageIndex-1)+1 40 SET @max=@pageIndex*@pageSize 41 --同样因为是组合字符串,所以必须把分页数值类型转化为字符串,否则报错 42 SET @sqlPage='SELECT * FROM 43 (SELECT ROW_NUMBER() OVER ('+@sort+'ORDER BY ID DESC)AS RowNumber,* FROM MES_Base_PDO)AS PageData 44 WHERE (RowNumber BETWEEN '+CAST(@min AS NVARCHAR(10))+' AND '+CAST(@max AS NVARCHAR(10)) 45 +') AND (IsDelete=0)'+@conditions 46 EXEC (@sqlPage) 47 48 DECLARE @sqlCount NVARCHAR(500) 49 SET @sqlCount='SELECT @pageCount=COUNT(*) FROM MES_Base_PDO WHERE IsDelete=0'+@conditions 50 declare @recordCount int 51 exec sp_executesql @sqlCount, N'@pageCount int output', @pageCount OUTPUT 52 END 53 --exec sql和exec(sql)是有区别的. 54 --exec sql是执行存储过程. 55 --exec(sql)是执行sql字符串语句. 56 -- 57 --EXEC (@sqlCount)
posted on 2016-01-05 18:19 LiGengMing 阅读(444) 评论(0) 编辑 收藏 举报