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)