存储过程中sp_executesql执行sql语句带参数问题

hankai15138325673 2015-10-03 04:45:35
小弟这个分页存储过程中where条件需要parameter参数化传递条件,不明白C#中如何将parameter一并加入进来



ALTER PROCEDURE [dbo].[sp_TablesPage]
(
@tblName nvarchar(1000), ----要显示的表或多个表的连接
@fields nvarchar(1000)='*', ----要显示的字段列表
@sortfields nvarchar(100)='', ----如果多列排序,一定要带asc或者desc,则@singleSortType排序方法无效,反之,单列根据@singleSortType来处理
@singleSortType int = 1, ----排序方法,0为升序,1为降序
@pageSize int = 10, ----每页显示的记录个数
@pageIndex int = 1, ----要显示那一页的记录
@strCondition nvarchar(1000)='', ----查询条件,不需where
@Counts int =1 output ----查询到的记录数
)

AS
/*
作者:
时间:2015-08-26
功能:实现多表分页查询;
*/
declare @sqlTmp nvarchar(2000)
declare @sqlGetCount nvarchar(2000)
declare @frontOrder nvarchar(200)
declare @behindOrder nvarchar(200)
declare @start nvarchar(20)
declare @end nvarchar(20)
declare @pageCount INT
if @strCondition=''
set @strCondition=' where 1=1'
else
set @strCondition=' where '+@strCondition
begin
if charindex(',',@sortfields,0)>0--多列排序,则@singleSortType排序方法无效
begin
set @frontOrder = @sortfields--获取分页前半部分数据的排序规则
set @behindOrder = replace(@frontOrder,'desc','desctmp')
set @behindOrder = replace(@behindOrder,'asc','desc')
set @behindOrder = replace(@behindOrder,'desctmp','asc')--获取分页后半部分数据的排序规则
end
else--单列
begin
set @sortfields=replace(replace(@sortfields,'desc',''),'asc','')--替换掉结尾的规则,用@singleSortType值来处理排序
if @singleSortType=1--倒序
begin
set @frontOrder = @sortfields + ' desc'
set @behindOrder = @sortfields + ' asc'
end

else
begin
set @frontOrder = @sortfields + ' asc'
set @behindOrder = @sortfields + ' desc'
end
end

--获取记录数

set @sqlGetCount = 'select @Counts=count(*) from ' + @tblName + @strCondition


----取得查询结果总数量-----
exec sp_executesql @sqlGetCount,N'@Counts int out ',@Counts out
declare @tmpCounts int
if @Counts = 0
set @tmpCounts = 1
else
set @tmpCounts = @Counts

--取得分页总数
set @pageCount=(@tmpCounts+@pageSize-1)/@pageSize

/**当前页大于总页数 取最后一页**/
if @pageIndex>@pageCount
set @pageIndex=@pageCount
/*-----数据分页2分处理-------*/
declare @pageIndexNew int --总数/页大小
declare @lastcount int --总数%页大小 最后一页的数据量

set @pageIndexNew = @tmpCounts/@pageSize
set @lastcount = @tmpCounts%@pageSize
if @lastcount > 0
set @pageIndexNew = @pageIndexNew + 1
else
set @lastcount = @pagesize

--取得数据的逻辑分析
if @pageIndexNew<2 or @pageIndex<=@pageIndexNew / 2 + @pageIndexNew % 2 --前半部分数据处理
begin
--计算开始结束的行号
set @start = @pageSize*(@pageIndex-1)+1
set @end = @start+@pageSize-1
set @sqlTmp='SELECT * FROM (select '+@fields+',ROW_NUMBER() OVER ( Order by '+@frontOrder+' ) AS RowNumber From '+@tblName+@strCondition+') T WHERE T.RowNumber BETWEEN '+@start+' AND '+@end
end
else
begin
set @pageIndex = @pageIndexNew-@pageIndex+1 --后半部分数据处理
if @lastcount=@pageSize --如果正好是整数页
begin
set @start = @pageSize*(@pageIndex-1)+1
set @end = @start+@pageSize-1
end
else
begin
set @start = @lastcount+@pageSize*(@pageIndex-2)+1
set @end = @start+@pageSize-1
end
set @sqlTmp='select top '+@end+' * FROM (select '+@fields+',ROW_NUMBER() OVER ( Order by '+@behindOrder+' ) AS RowNumber From '+@tblName+@strCondition+') T WHERE T.RowNumber BETWEEN '+@start+' AND '+@end+' order by RowNumber desc '
end
end
exec sp_executesql @sqlTmp


调用:


public DataTable TablesPage(string tableName, string fields, string sortfield, bool singleSortType, int pageSize, int pageIndex, string condition, out int count, List<IDbDataParameter> listParameters = null)
{
var outParmeter = DbManager.OutputParameter("@Counts", SqlDbType.Int);
ListParameters = new List<IDbDataParameter>
{
DbManager.InputParameter("@tblName", tableName),
DbManager.InputParameter("@fields", fields),
DbManager.InputParameter("@sortfields", sortfield),
DbManager.InputParameter("@singleSortType", singleSortType ? "1" : "0"),
DbManager.InputParameter("@pageSize", pageSize),
DbManager.InputParameter("@pageIndex", pageIndex),
DbManager.InputParameter("@strCondition", condition),
outParmeter
};
DbManager.Command.Parameters.Add(new SqlParameter("@@ForumId", "123"));
//if (listParameters != null) ListParameters.AddRange(listParameters);
var dt = DbManager.SetCommand(CommandType.StoredProcedure, "sp_TablesPage", ListParameters.ToArray()).ExecuteDataTable();
count = (int)outParmeter.Value;
return dt;
}


用的BLToolkit这个ORM
...全文
553 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
demon2008d 2015-10-08
  • 打赏
  • 举报
回复
--数值型的判断参数,合并成取判断值的语句 declare @m_sentence NVarchar(2000); set @m_sentence = 'select @m_par_num=par_num from youtable where primary_col=1'; exec sp_executesql @m_sentence,N'@m_par_num numeric(18,0) output',@m_par_num output;
Tiger_Zhao 2015-10-08
  • 打赏
  • 举报
回复
ALTER PROCEDURE [dbo].[sp_TablesPage]
(
@tblName nvarchar(1000), ----要显示的表或多个表的连接
@fields nvarchar(1000)='*', ----要显示的字段列表
@sortfields nvarchar(100)='', ----如果多列排序,一定要带asc或者desc,则@singleSortType排序方法无效,反之,单列根据@singleSortType来处理
@singleSortType int = 1, ----排序方法,0为升序,1为降序
@pageSize int = 10, ----每页显示的记录个数
@pageIndex int = 1, ----要显示那一页的记录
@strCondition nvarchar(1000)='', ----查询条件,不需where
@Counts int =1 output ----查询到的记录数
)

C#只要关心参数部分,下面和调用无关。
8个参数对8个参数,所以额外添加 @@ForumId 参数是错误的。
h_gxi 2015-10-03
  • 打赏
  • 举报
回复
引用 2 楼 hankai15138325673 的回复:
忘了说一句 DbManager.Command.Parameters.Add(new SqlParameter("@ForumId", "123")); @ForumId是where 条件中的参数 where = " ForumId=@ForumId ";
Parameter就是参数,你的存储过程里没有@ForumId参数,怎么传进去 应该@strCondition 参数才是where附加条件的参数
LongRui888 2015-10-03
  • 打赏
  • 举报
回复
好复杂啊 exec sp_executesql @sqlGetCount,N'@Counts int out ',@Counts out 实际上 就是通过这个sp_executesql来执行存储过程时,会定义变量 @Counts ,然后执行你的语句把值放到@Counts 里,最后返回。 另外,本质上这个和exec语句是一样的都是动态执行sql。
hankai15138325673 2015-10-03
  • 打赏
  • 举报
回复
忘了说一句 DbManager.Command.Parameters.Add(new SqlParameter("@ForumId", "123")); @ForumId是where 条件中的参数 where = " ForumId=@ForumId ";
hankai15138325673 2015-10-03
  • 打赏
  • 举报
回复
在线等...急....
SQL Server EXEC 与 SP_EXECUTESQL 的区别 MSSQL为我们提供了两种动态执行SQL语句的命令,分别是 EXEC 和 SP_EXECUTESQL ,我们先来看一下两种方式的用法。 先建立一个表,并添加一些数据来进行演示: 复制代码 CREATE TABLE t_student( Id INT NOT NULL, Name NVARCHAR (10) NULL, Age TINYINT NULL, School NVARCHAR(20) NULL, Class NVARCHAR(10) NULL, Score FLOAT NULL, CONSTRAINT [PK_Student_Id] PRIMARY KEY CLUSTERED(Id) ) GO INSERT INTO t_student VALUES(1,'张小红',8,'育才小学','一班',92) INSERT INTO t_student VALUES(2,'王丽丽',8,'育才小学','一班',90) INSERT INTO t_student VALUES(3,'张燕',7,'云华小学','二班',86) INSERT INTO t_student VALUES(4,'刘华',6,'云华小学','二班',85) 复制代码 一、EXEC EXEC命令可以执行一个存储过程也可以执行一个动态SQL语句。先来看看怎么执行存储过程: 新建一个存储过程 SP_GetStudent ,返回 成绩大于90 分的学生: 复制代码 CREATE PROCEDURE [dbo].[Sp_GetStudent] @Score FLOAT, @Nums INT OUTPUT AS BEGIN SET NOCOUNT ON; SELECT * FROM t_student WHERE Score >=@Score SELECT @Nums=COUNT(1) FROM t_student WHERE Score >=@Score IF(@Nums>0) RETURN 1 ELSE RETURN 0 END GO 复制代码 该存储过程涉及了 查询操作、返回值和输出参数,我们来看用EXEC 命令如何调用: 复制代码 DECLARE @return_value int, @OutNums int EXEC @return_value = [dbo].[Sp_GetStudent] @Score = 90, @Nums = @OutNums OUTPUT SELECT @OutNums as N'大于90分的人数' SELECT '返回值' = @return_value GO 复制代码 执行结果: 我们发现EXEC 执行存储过程和我们平时程序执行一个方法是几乎一样的,返回值参数 直接就可以等于存储过程执行后的返回值,输出参数 在后面需要增加 OUTPUT 关键字。 执行存储过程不是重点,重点是执行动态sql语句,同样看一下例子: DECLARE @TableName NVARCHAR(50),@Sql NVARCHAR(MAX),@Score INT; SET @TableName = 't_Student'; SET @Score = 90; SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE Score >= '+CAST(@Score AS NVARCHAR(10)) EXEC (@sql); 执行结果: 注意:在执行拼接SQL 语句的时候,的EXEC括号只允许包含一个字符串变量,但是可以串联多个变量,如果我们直接执行这个SQL语句: --这是错误的调用 EXEC ('SELECT * FROM '+QUOTENAME(@TableName) +'WHERE Score >= '+CAST(@Score AS NVARCHAR(10))); 执行就会提示错误。但是这样就没有问题: 复制代码 DECLARE @TableName NVARCHAR(50),@Sql NVARCHAR(MAX),@Score INT DECLARE @Sql2 NVARCHAR(MAX) SET @TableName = 't_Student'; SET @Score = 90; SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) SET @Sql2=' WHERE Score >= '+CAST(@Score AS NVARCHAR(10)) EXEC (@sql+@sql2) 复制代码 EXEC 执行拼接sql语句的时候不支持 嵌入式参数,如下: DECLARE @OUT_Nums INT,@IN_Score INT,@Sql NVARCHAR(MAX) SET @IN_Score = 90 SET @sql = 'SELECT @Nums=COUNT(1) FROM t_student WHERE Score >= @Score' EXEC (@sql) 通过上面的代码发现,EXEC 执行拼接的SQL语句的时候,不支持内嵌参数,包括输入参数和输出参数。有的时候我们想把得到的count(*)传出来,用EXEC是不好办到的。接下来,再来看看SP_EXECUTESQL的使用: 二、SP_EXECUTESQLSP_EXECUTESQL 是在 SQL 2005引入的新的系统存储过程,也是用来处理动态SQL 语句的。它比EXEC 更加灵活,首先也执行一下第一次的拼接SQL语句: DECLARE @TableName NVARCHAR(50),@Sql NVARCHAR(MAX),@Score INT; SET @TableName = 't_Student'; SET @Score = 90; SET @sql = 'SELECT * FROM '+QUOTENAME(@TableName) +'WHERE Score >= '+CAST(@Score AS NVARCHAR(10)) EXEC SP_EXECUTESQL @sql --注意这里没有了() 执行结果: SP_EXECUTESQL 支持内嵌参数: 先来看一下SP_EXECUTESQL的语法: sp_executesql [ @stmt = ] stmt [ {, [@params=] N'@parameter_name data_type [ OUT | OUTPUT ][,...n]' } {, [ @param1 = ] 'value1' [ ,...n ] } ] 说明: [ @stmt = ] stmt 包含 Transact-SQL 语句或批处理的 Unicode 字符串。stmt 必须是 Unicode 常量或 Unicode 变量。不允许使用更复杂的 Unicode 表达式(例如使用 + 运算符连接两个字符串)。不允许使用字符常量。如果指定了 Unicode 常量,则必须使用 N 作为前缀。例如,Unicode 常量 N'sp_who' 是有效的,但是字符常量 'sp_who' 则无效。字符串的大小仅受可用数据库服务器内存限制。在 64 位服务器,字符串大小限制为 2 GB,即 nvarchar(max) 的最大大小。stmt 包含的每个参数在 @params 参数定义列表和参数值列表均必须有对应项 [ @params = ] N'@parameter_namedata_type[ ,... n ] ' 包含 stmt 嵌入的所有参数定义的字符串。字符串必须是 Unicode 常量或 Unicode 变量。每个参数定义由参数名称和数据类型组成。n 是表示附加参数定义的占位符。在 stmt 指定的每个参数必须在 @params 定义。如果 stmt 的 Transact-SQL 语句或批处理不包含参数,则不需要 @params。该参数的默认值为 NULL。 [ @param1 = ] 'value1' 参数字符串定义的第一个参数的值。该值可以是 Unicode 常量,也可以是 Unicode 变量。必须为 stmt 包含的每个参数提供参数值。如果 stmt 的 Transact-SQL 语句或批处理没有参数,则不需要这些值。 [ OUT | OUTPUT ] 指示参数是输出参数。除非是公共语言运行 (CLR) 过程,否则 text、ntext 和 image 参数均可用作 OUTPUT 参数。使用 OUTPUT 关键字的输出参数可以为游标占位符,CLR 过程除外。 n 附加参数值的占位符。这些值只能为常量或变量,不能是很复杂的表达式(例如函数)或使用运算符生成的表达式。 返回代码值 : 0(成功)或非零(失败) 结果集:从生成 SQL 字符串的所有 SQL 语句返回结果集 看不懂没有关系,通过例子就会非常明白的,依旧还执行上面的 SQL 语句: DECLARE @OUT_Nums INT,@IN_Score INT,@Sql NVARCHAR(MAX) SET @IN_Score = 90 SET @sql = 'SELECT @Nums=COUNT(1) FROM t_student WHERE Score >= @Score' EXEC SP_EXECUTESQL @sql,N'@Nums INT OUT,@Score INT',@OUT_Nums OUTPUT,@IN_Score SELECT @OUT_Nums AS '人数' 执行结果: 需要注意的是: 1、要求动态Sql和动态Sql参数列表必须是NVARCHAR 2、动态Sql参数列表与外部提供值的参数列表顺序必需一致 3、一旦使用了 '@name = value' 形式之后,所有后续的参数就必须以 '@name = value' 的形式传递,比如: DECLARE @OUT_Nums INT,@IN_Score INT,@Sql NVARCHAR(MAX) SET @IN_Score = 90 SET @sql = 'SELECT @Nums=COUNT(1) FROM t_student WHERE Score >= @Score' EXEC SP_EXECUTESQL @stmt=@sql,@params=N'@Nums INT OUT,@Score INT',@Nums=@OUT_Nums OUTPUT,@Score=@IN_Score SELECT @OUT_Nums AS '人数' 通过上面的例子已经很清晰的表明了,在执行动态SQL 语句的时候,EXEC 和 SP_EXECUTESQL 的区别了,来总结一下: 1、 性能: 官方描述:sp_executesql stmt 参数的 Transact-SQL 语句或批处理在执行 sp_executesql 语句时才编译。随后,将编译 stmt 的内容,并将其作为执行计划运行。该执行计划独立于名为 sp_executesql 的批处理的执行计划。sp_executesql 批处理不能引用调用 sp_executesql 的批处理声明的变量。sp_executesql 批处理的本地游标或变量对调用 sp_executesql 的批处理是不可见的。对数据库上下文所做的更改只在 sp_executesql 语句结束前有效。如果只更改了语句参数值,则 sp_executesql 可用来代替存储过程多次执行 Transact-SQL 语句。因为 Transact-SQL 语句本身保持不变,仅参数值发生变化,所以 SQL Server 查询优化器可能重复使用首次执行时所生成的执行计划。 说通俗一点就是:如果用 EXEC 执行一条动态 SQL 语句,由于每次传入的参数不一样,所以每次生成的 @sql 就不一样,这样每执行一次SQL SERVER 就必须重新将要执行的动态 Sql 重新编译一次 。但是SP_EXECUTESQL 则不一样,由于将数值参数化,要执行的动态 Sql 永远不会变化,只是传入的参数的值在变化,那每次执行的时候就不用重新编译,速度和效率自然有所提升。 2、从上面的例子我们已经能够看出 SP_EXECUTESQL 命令比 EXEC 命令更灵活,因为它提供一个接口,该接口及支持输入参数也支持输出参数。 3、EXEC 执行纯动态SQL执行时可能无法使用预编译的执行计划,关键是不安全,可以导致 SQL 注入 ,而 SP_EXECUTESQL 执行参数化动态 SQL ,执行时能使用预编译的执行计划,而且保存存储过程时就可以确定可以使用的预编译的执行计划,而且最重要的是“安全”,天然免疫SQL 注入 作者:Rising Sun 出处:http://www.cnblogs.com/lxblog/ 本文版权归作者和博客园共有,欢迎转载,但未经作者同意必须保留此段声明,且在文章页面明显位置给出原文连接,否则保留追究法律责任的权利.

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧