修复一个通用存储过程ProcGetPageData的bug

修复一个通用存储过程ProcGetPageData的bug

2393发表于2015-10-16

今天在做一个功能的时候遇到了一个问题让找了好久,直到把分页存储过程最后拼接出来的sql语句打印出来才找到问题的原因。

报错如下:

“System.Data.SqlClient.SqlException”类型的异常在 System.Data.dll 中发生,但未在用户代码中进行处理

其他信息: 在应使用条件的上下文(在 'Cre' 附近)中指定了非布尔类型的表达式。

在应使用条件的上下文(在 'ORDER' 附近)中指定了非布尔类型的表达式。


我使用的框架是dapper+asp.net mvc5+存储过程分页。我之前也写写过一篇mvc+dapper的通用分页,ASP.NET MVC基于dapper的通用万能的泛型分页实例。

这里分页criteria.TableName用到的是子查询虚拟表,其中关联了几个表,sql 语句也比较长和复杂。

原来的存储过程ProcGetPageData:

Create PROCEDURE [dbo].[ProcGetPageData] 
( @TableName VARCHAR(1000), --表名,多表是请使用 tA a inner join tB b On a.AID = b.AID 
@PrimaryKey NVARCHAR(100), --主键,可以带表头 a.AID 
@Fields NVARCHAR(2000) = '*',--读取字段 
@Condition NVARCHAR(3000) = '',--Where条件 
@CurrentPage INT = 1, --开始页码 
@PageSize INT = 10, --页大小 
@Sort NVARCHAR(200) = '', --排序字段 
@RecordCount INT = 0 OUT 
) 
AS 
DECLARE @strWhere VARCHAR(2000) 
DECLARE @strsql NVARCHAR(3900) 
IF @Condition IS NOT NULL AND len(ltrim(rtrim(@Condition)))>0 
BEGIN 
	SET @strWhere = ' WHERE ' + @Condition + ' ' 
	END 
	ELSE 
	BEGIN 
	SET @strWhere = '' 
END 
 
IF (charindex(ltrim(rtrim(@PrimaryKey)),@Sort)=0) 
BEGIN 
	IF(@Sort='') 
	SET @Sort = @PrimaryKey + ' DESC ' 
	ELSE 
	SET @Sort = @Sort+ ' , '+@PrimaryKey + ' DESC ' 
END 
SET @strsql = 'SELECT @RecordCount = Count(1) FROM ' + @TableName + @strWhere 
EXECUTE sp_executesql @strsql ,N'@RecordCount INT output',@RecordCount OUTPUT 

IF @CurrentPage = 1 --第一页提高性能 
	BEGIN 
		SET @strsql = 'SELECT TOP ' + str(@PageSize) +' '+@Fields 
		+ ' FROM ' + @TableName + ' ' + @strWhere + ' ORDER BY '+ @Sort 
	END 
ELSE 
	BEGIN 
		/* 分页的动态sql语句 */ 
		DECLARE @START_ID NVARCHAR(50) 
		DECLARE @END_ID NVARCHAR(50) 
		SET @START_ID = CONVERT(NVARCHAR(50),(@CurrentPage - 1) * @PageSize + 1) 
		SET @END_ID = CONVERT(NVARCHAR(50),@CurrentPage * @PageSize) 
		SET @strsql = ' SELECT * 
		FROM (SELECT ROW_NUMBER() OVER(ORDER BY '+@Sort+') AS rownum, 
		'+@Fields+ ' 
		FROM '+@TableName + @strWhere +') AS XX 
		WHERE rownum BETWEEN '+@START_ID+' AND ' +@END_ID +' ORDER BY XX.rownum ASC' 
	END 
EXEC(@strsql) 
RETURN 

我调试的时候我把存储过程的变量@trsql 用print打印出来,执行sql的关键语句:

EXECUTE sp_executesql @strsql ,N'@RecordCount INT output',@RecordCount OUTPUT
EXEC(@strsql) 

上面两行都注释掉,也就是不执行这个动态sql语句。

最后看到这个存储过程返回来的sql语名被截断了,于是我怀疑是不是存储过程定义的@table变量的长度1000是不是不够了。然后我就把存储过程ProcGetPageData的参数@table改成4000,果然就可以了。完美收工。

总结:

sql语句报错或者结果不是我们要的时一定要把最终执行的sql语句拿出来在查询分析器中执行一下,这样更好看是哪里出了问题。有时候参数的值的长度超了参数定义的长度,sql server会自动为我们截断。这一点可能有时想到了,但是它反而恰恰是出现问题的原因。


小编蓝狐