用存储过程生成分页的where条件拼接SQL

先说说场景吧， 为什么需要这个。

分页， 一般是在程序里面做的， 如果有必要就加上某个条件， 没必要则不加， 这样形成的 SQL 没得说， 非常清晰。

但分页能不能用存储过程来做呢？

其实也是可以的， 有两种方式：

1. 类似：

ISNULL(@fbillno,'')='' OR t1.fbillno = @fbillno

这种方法非常简单， 不需要用到动态SQL,  但效率不高——用不到索引。数据量大时会有很大问题。

2. 跟程序里一样， 拼接SQL,  最后调用动态SQL。

但参数如果有好多个， 自己写这些条件的SQL， 难免索然无味。 

所以， 就有了这篇小文。

最初的想法来自于这个贴子：点击打开链接

下面是脚本：

IF OBJECT_ID('dbo.Proc_GetPageSearchCondition') IS NOT NULL 
	DROP PROC dbo.Proc_GetPageSearchCondition
GO
-- =============================================
-- Author:		yenange
-- Create date: 2017-07-14
-- Description:	根据给定的条件参数，得到最后的条件SQL
-- 说明：
-- 1. 多个参数在字符串内以换行隔开;
-- 2. 每个参数的格式为4部分(分隔符分别为：空格、|、^):
--    参数名称 参数类型|如果参数有值(或自定义条件满足)则需要拼接的SQL^自定义的条件
-- 3. 参数中的第4部分：自定义条件为可选，不是必须的
-- =============================================
CREATE PROC dbo.Proc_GetPageSearchCondition 
(
	--获取总数SQL
	@sql_cnt NVARCHAR(MAX)='SELECT COUNT(1) FROM dc_SubDb_sql WHERE 1=1 AND [IsDeleted] = 0 %where%'
	--获取分页数据SQL
	,@sql_data NVARCHAR(MAX)=
'SELECT * FROM (
	SELECT ROW_NUMBER() OVER ( ORDER BY sqlId ASC ) rid
	,* 
	from dc_SubDb_sql
	WHERE 1=1 AND [IsDeleted] = 0 %where%
) p_paged
WHERE rid>( (@pageIndex-1)*@pageSize ) AND rid<= (@pageIndex*@pageSize)
ORDER BY rid'	
	--参数串		
	,@paras NVARCHAR(MAX)='@sqlName nvarchar(50)|sqlName like ''%''+@sqlName+''%'',
	 @sqlType INT|sqlType=@sqlType^ISNULL(@sqlType,-1)>-1,
	 @remark NVARCHAR(50)|remark like ''%''+@remark+''%'',
	 @release BIT|release=@release'
)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @r NVARCHAR(MAX),@paraStr NVARCHAR(MAX),@paraActual NVARCHAR(MAX)
	--1. 按行分隔，将每个参数放在一行
	DECLARE @t TABLE (rowNum INT IDENTITY PRIMARY KEY
		,line NVARCHAR(MAX)
		,paraName AS rtrim(ltrim(SUBSTRING(line,1,CHARINDEX(' ',line))))  		--从最开始到第一个空格，视为参数名称
		,paraType AS SUBSTRING(line,CHARINDEX(' ',line),CHARINDEX('|',line)-CHARINDEX(' ',line)) 	--从第一个空格，到最后，视为参数类型
		,expression AS REPLACE(SUBSTRING(line,CHARINDEX('|',line)+1
			,CASE WHEN CHARINDEX('^',line)>0 THEN CHARINDEX('^',line)-CHARINDEX('|',line)-1 ELSE LEN(line) END ),'''','''''')
		,customCondition AS CASE WHEN CHARINDEX('^',line)>0 THEN REPLACE(SUBSTRING(line, CHARINDEX('^',line)+1, LEN(line)),'''','''''') 
			ELSE '' END
	)
	WHILE CHARINDEX(CHAR(10),@paras)>0  
    BEGIN  
        INSERT INTO @t(line)  
        SELECT SUBSTRING(@paras,1,CHARINDEX(char(10),@paras))  
        SET @paras = SUBSTRING(@paras,CHARINDEX(char(10),@paras)+1,LEN(@paras))  
    END  
    IF LEN(@paras)>0  
    BEGIN  
        INSERT INTO @t(line) VALUES(@paras)  
    END  
    UPDATE @t SET line=rtrim(ltrim(REPLACE(REPLACE(REPLACE(line,char(13),''),CHAR(10),''),CHAR(9),''))) 
	UPDATE @t SET line=CASE WHEN RIGHT(line,1)=',' THEN LEFT(line,LEN(line)-1) ELSE line END
	--SELECT * FROM @t
	SELECT @paraStr=STUFF(
			( SELECT ','+a.paraName+' '+a.paraType+'|' FROM @t AS a FOR XML PATH('') )
		,1,1,'')
	SET @paraStr=REPLACE(SUBSTRING(@paraStr,1,LEN(@paraStr)-1),'|','
')
	
	SELECT @paraActual=STUFF(
			( SELECT ','+a.paraName+'|' FROM @t AS a FOR XML PATH('') )
		,1,1,'')
	SET @paraActual=REPLACE(SUBSTRING(@paraActual,1,LEN(@paraActual)-1),'|','
')
		
	--遍历所有行，得到条件SQL
	DECLARE @i INT,@iMax INT,@paraName NVARCHAR(MAX),@paraType NVARCHAR(MAX),@expression NVARCHAR(MAX),@customCondition NVARCHAR(MAX)
	SELECT @i=1,@iMax=ISNULL(MAX(rowNum),0) FROM @t
	SET @r='
SET NOCOUNT ON
DECLARE @sql_cnt NVARCHAR(MAX),@sql_data NVARCHAR(MAX),@where NVARCHAR(MAX),@sql nvarchar(max)
SET @sql_cnt = '''+REPLACE(@sql_cnt,'''','''''')+' ''
SET @sql_data = '''+REPLACE(@sql_data,'''','''''')+' ''
SET @where = ''''

'
	WHILE @i<=@iMax
	BEGIN
		SELECT 
			@paraName=paraName
			,@paraType=paraType
			,@expression=expression
			,@customCondition=customCondition
		FROM @t WHERE rowNum=@i
		SET @r=@r+'-- '+CAST(@i AS VARCHAR(10)) + '. ' + @paraName +'
'
		IF ISNULL(@customCondition,'')!=''
		BEGIN
			SET @r=@r+'IF '+@customCondition
		END
		ELSE IF @paraType LIKE '%char%'
		BEGIN
			SET @r=@r+'IF ISNULL('+@paraName+','''') != '''' '
		END
		ELSE IF @paraType LIKE '%text%'
		BEGIN
			SET @r=@r+'IF ISNULL(CAST('+@paraName+' AS nvarchar(max)),'''') != '''' '
		END
		ELSE
		BEGIN
			SET @r=@r+'IF '+@paraName+' IS NOT NULL '
		END
		SET @r=@r+'
BEGIN
	SET @where = @where + '' AND '+@expression+' ''
END
'
		SET @i=@i+1
	END
	
	SET @r=@r+'
--取总数
SET @sql_cnt = REPLACE( @sql_cnt, ''%where%'' , @where )
EXEC sp_executesql @sql_cnt
,N'''+@paraStr+'''
,'+@paraActual+'

--取分页数据
SET @sql_data = REPLACE( @sql_data, ''%where%'' , @where )
EXEC sp_executesql @sql_data
,N''@pageIndex INT
,@pageSize INT
,'+@paraStr+'''
,@pageIndex
,@pageSize
,'+@paraActual
	
	PRINT @r
END
GO
--测试
EXEC Proc_GetPageSearchCondition 
	--获取总数SQL
	@sql_cnt ='SELECT COUNT(1) FROM dc_SubDb_sql WHERE 1=1 AND [IsDeleted] = 0 %where%'
	--获取分页数据SQL
	,@sql_data =
'SELECT * FROM (
	SELECT ROW_NUMBER() OVER ( ORDER BY sqlId ASC ) rid
	,* 
	from dc_SubDb_sql
	WHERE 1=1 AND [IsDeleted] = 0 %where%
) p_paged
WHERE rid>( (@pageIndex-1)*@pageSize ) AND rid<= (@pageIndex*@pageSize)
ORDER BY rid'	
	--参数串		
	,@paras ='@sqlName nvarchar(50)|sqlName like ''%''+@sqlName+''%'',
	 @sqlType INT|sqlType=@sqlType^ISNULL(@sqlType,-1)>-1,
	 @remark NVARCHAR(50)|remark like ''%''+@remark+''%'',
	 @release BIT|release=@release'
/*
------ 下面是执行得到的结果，复制到分页存储过程即可，不需要再写一行代码 ------
SET NOCOUNT ON
DECLARE @sql_cnt NVARCHAR(MAX),@sql_data NVARCHAR(MAX),@where NVARCHAR(MAX),@sql nvarchar(max)
SET @sql_cnt = 'SELECT COUNT(1) FROM dc_SubDb_sql WHERE 1=1 AND [IsDeleted] = 0 %where% '
SET @sql_data = 'SELECT * FROM (
	SELECT ROW_NUMBER() OVER ( ORDER BY sqlId ASC ) rid
	,* 
	from dc_SubDb_sql
	WHERE 1=1 AND [IsDeleted] = 0 %where%
) p_paged
WHERE rid>( (@pageIndex-1)*@pageSize ) AND rid<= (@pageIndex*@pageSize)
ORDER BY rid '
SET @where = ''

-- 1. @sqlName
IF ISNULL(@sqlName,'') != '' 
BEGIN
	SET @where = @where + ' AND sqlName like ''%''+@sqlName+''%'' '
END
-- 2. @sqlType
IF ISNULL(@sqlType,-1)>-1
BEGIN
	SET @where = @where + ' AND sqlType=@sqlType '
END
-- 3. @remark
IF ISNULL(@remark,'') != '' 
BEGIN
	SET @where = @where + ' AND remark like ''%''+@remark+''%'' '
END
-- 4. @release
IF @release IS NOT NULL 
BEGIN
	SET @where = @where + ' AND release=@release '
END

--取总数
SET @sql_cnt = REPLACE( @sql_cnt, '%where%' , @where )
EXEC sp_executesql @sql_cnt
,N'@sqlName  nvarchar(50)
,@sqlType  INT
,@remark  NVARCHAR(50)
,@release  BIT'
,@sqlName
,@sqlType
,@remark
,@release

--取分页数据
SET @sql_data = REPLACE( @sql_data, '%where%' , @where )
EXEC sp_executesql @sql_data
,N'@pageIndex INT
,@pageSize INT
,@sqlName  nvarchar(50)
,@sqlType  INT
,@remark  NVARCHAR(50)
,@release  BIT'
,@pageIndex
,@pageSize
,@sqlName
,@sqlType
,@remark
,@release

*/