sqlserver 动态sql执行execute和sp_executesql

全栈程序员-站长 • 2022年5月22日上午6:20 • 未分类 • 阅读 39

大家好，又见面了，我是你们的朋友全栈君。

　　sqlserver 动态sql的执行,有两个方法execute和 sp_executesql.其中第一个方法execute可以简写为exec. execute方法适合执行没有返回值的动态sql,sp_executesql可以获取到动态sql的返回值.二者比较起来,前者写起来简单,后者功能强大些,但写起来麻烦,使用的时候具体情况具体分析吧.
　　在function 中不能使用exec和sp_executesql,但是存储过程中可以使用
　　1. execute 的运用

--1.查@psn_code 的申请数量
--但是我们没有在变量中得到数量为多少,只是在查询结果中看到的改数据.
declare @sql nvarchar(2000);
DECLARE @psn_code NVARCHAR(100);
SET @psn_code = 774;
set @sql='select count(*) from proposal where psn_code='+@psn_code;
EXECUTE(@sql);

　　2. sp_executesql的运用
　　书写语法要点:
　　exec sp_executesql @sql,N’参数1 类型1,参数2 类型2,参数3 类型3 OUTPUT’,参数1,参数2,参数3 OUTPUT;
　　注意参数前后顺序必须对应好如下图不同颜色的标记
　　这里写图片描述
　　
例1

---书写规则
---exec sp_executesql @sql,N'参数1 类型1,参数2 类型2,参数3 类型3 OUTPUT',参数1,参数2,参数3 OUTPUT;
---注意参数顺序必须对应好,习惯上把OUTPUT 参数放在最后
DECLARE @sql NVARCHAR(1000);
DECLARE @tableName NVARCHAR(100);
DECLARE @count INT;
DECLARE @psn_code NVARCHAR(100);
DECLARE @form_code NVARCHAR(100);

SET @psn_code = '774';
SET @form_code = '10005';

set @sql=N'select @count=count(1) from proposal where psn_code= @psn_code and form_code=@form_code';
exec sp_executesql @sql,N'@psn_code NVARCHAR(100),@form_code NVARCHAR(100),@count int OUTPUT',@psn_code,@form_code,@count OUTPUT;
print @count;

这里写图片描述

　　在测试过程中,将表名也使用参数绑定去发现错误.脚本如下
　　
反例2

--动态查出每个表中有多少条数据
DECLARE @sql NVARCHAR(1000);
DECLARE @tableName NVARCHAR(100);
DECLARE @count INT;
DECLARE @psn_code NVARCHAR(100);
DECLARE @status NVARCHAR(5);

SET @tableName = 'PROPOSAL';
SET @psn_code = '774';
SET @status = '90';

set @sql=N'select @count=count(1) from @tableName where psn_code= @psn_code and status = @status';
--注意这里表名不能是动态的参数,否则报错. 
--参数列表:N'参数1,参数2,参数3 OUTPUT',参数1,参数2,参数3 OUTPUT 前后顺序一定要对的上
EXEC sp_executesql @sql,N'@tableName NVARCHAR(100),@psn_code NVARCHAR(100),@status NVARCHAR(5),@count int output',@tableName,@psn_code,@status,@count OUTPUT;
print @count;

　　错误信息如下，暂未找到原因
　　这里写图片描述
　　

为了解决上面的问题，只能曲线救国了．使用字符串拼接来完成．
例3

--动态查出每个表中有多少条数据
DECLARE @sql NVARCHAR(1000);
DECLARE @tableName NVARCHAR(100);
DECLARE @count INT;
DECLARE @psn_code NVARCHAR(100);
DECLARE @status NVARCHAR(5);

SET @tableName = 'PROPOSAL';
SET @psn_code = '774';
SET @status = '90';

set @sql=N'select @count=count(1) from '+@tableName+ ' where psn_code= @psn_code and status = @status';
--参数列表:N'参数1,参数2,参数3 OUTPUT',参数1,参数2,参数3 OUTPUT 前后顺序一定要对的上
EXEC sp_executesql @sql,N'@psn_code NVARCHAR(100),@status NVARCHAR(5),@count int output',@psn_code,@status,@count OUTPUT;
print @count;

例4: 参数既是入参,也是返回值
此例中会用到自定义函数FN_GETXML ,该函数获取一个简单的XML. 点击FN_GETXML函数获取此文章开头定义了改函数

--动态修改xml 串里面的一个节点的值
DECLARE @sql NVARCHAR(4000);
DECLARE @xml XML;
DECLARE @xml2 XML;
DECLARE @node NVARCHAR(4000);
DECLARE @value NVARCHAR(4000);
SET @node= '(data/budget_list[1]/PrpManpowers/PrpManpower[1]/ename/text())[1] ';
SET @value = '11111111';
SET @xml = dbo.FN_GETXML();
---@xml 作为in/output
SET @sql = 'SET @xml.modify(''replace value of '+@node +' with "'+@value+'"'''+');';
EXECUTE sp_executesql @sql,N'@xml xml OUTPUT',@xml OUTPUT;
SELECT @xml;