SQL SERVER EXEC 、SP_EXECUTESQL

EXEC

Exec 命令有两种用法
1、执行存储过程
2、执行动态的批处理

exec 存储过程 @参数 = 值    
--或    
exec 存储过程 值 

exec 存储过程  存储过程中的参数=参数{接受参数返回值} output

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
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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

exec ('select * from mytable')  
1

使用EXEC执行动态sql语句注意下面问题
1.不能有输入参数，输出参数

DECLARE @i AS INT;  
SET @i = 10248;  
DECLARE @sql AS VARCHAR(52);  
SET @sql = 'SELECT * FROM dbo.Orders WHERE OrderID = @i;';  
EXEC(@sql);  
GO  
1
2
3
4
5
6

2.圆括号内部能使用函数或case表达式

----下面的脚本是错误的：
DECLARE @schemaname AS NVARCHAR(128), @tablename AS NVARCHAR(128);  
SET @schemaname = N'dbo';  
SET @tablename = N'Order Details';  
EXEC(N'SELECT COUNT(*) FROM '  
     + QUOTENAME(@schemaname) + N'.' + QUOTENAME(@tablename) + N';');  
GO 
------不过把函数放在变量中是可以的：
DECLARE  
  @schemaname AS NVARCHAR(128),  
  @tablename AS NVARCHAR(128),  
  @sql AS NVARCHAR(539);  
SET @schemaname = N'dbo';  
SET @tablename = N'Order Details';  
SET @sql = N'SELECT COUNT(*) FROM '  
  + QUOTENAME(@schemaname) + N'.' + QUOTENAME(@tablename) + N';'  
EXEC(@sql);  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3.不能利用重用执行计划，存所以存在性能问题

DECLARE @i AS INT;  
SET @i = 10248;  
  
DECLARE @sql AS VARCHAR(52);  
SET @sql = 'SELECT * FROM dbo.Orders WHERE OrderID = '  
  + CAST(@i AS VARCHAR(10)) + N';';  
EXEC(@sql);  
GO  
1
2
3
4
5
6
7
8

4、容易被sql注入，存在安全问题。

DECLARE @lastname AS NVARCHAR(40), @sql AS NVARCHAR(200);  
SET @lastname = N''' DROP TABLE dbo.Employees --';  
SET @sql = N'SELECT * FROM dbo.Employees WHERE LastName = '''  
  + @lastname + ''';';  
EXEC @sql;  
GO 
--实际sql SELECT * FROM dbo.Employees WHERE LastName = '' DROP TABLE dbo.Employees --';
1
2
3
4
5
6
7

注意

EXEC 执行拼接的SQL语句的时候，不支持内嵌参数，包括输入参数和输出参数。有的时候我们想把得到的count(*)传出来，用EXEC是不好办到的

sp_executesql

重用执行计划提供更好的支持
语法结构
sp_executesql [ @stmt = ] stmt
[
{, [@params=] N’@parameter_name data_type [ OUT | OUTPUT ][,…n]’ }
{, [ @param1 = ] ‘value1’ [ ,…n ] }
]

exec sp_executesql
@stmt=<statement>,----类似存储过程的主体
@params=<params>,-----类似存储过程的参数部分
@<params assignment>----类似存储过程的调用
--@stmt参数是输入的动态批处理，它可以引入输入参数或输出参数，和存储过程的主体语句一样，只不过它是动态的，而存储过程是静态的，不过你可以在存储过程中使用sp_executesql
---@params参数于定义输入、输出参数的存储过程头类似，实际上和存储过程的语法完全一样
--@与调用存储过程的exec部分类似
1
2
3
4
5
6
7

DECLARE @TotalSqlStr NVARCHAR(MAX);
SET @TotalSqlStr = 'SELECT @TotalRecords = COUNT(p.Id) FROM (SELECT ' + @MainSqlStr + @FilterStr + ') AS p';

EXEC sp_executesql 
@TotalSqlStr, 
N'@TotalRecords INT OUTPUT', 
@TotalRecords OUTPUT
1
2
3
4
5
6
7

如果你要把输出返回给调用批处理中的变量，事情就没有那么简单了。为此，你必须使用INSERT EXEC语法把输出插入到一个目标表中，然后从这表中获取值后赋给该变量，就像这样：

DECLARE @sql NVARCHAR(MAX),@RecordCount INT
SET @sql = 'SELECT COUNT(ORDERID) FROM Orders'; 
CREATETABLE #T(TID INT);
INSERT INTO #T EXEC(@sql);   --这里是一个表格
SET @RecordCount = (SELECT TID FROM #T)
SELECT @RecordCount
DROP TABLE #T

1
2
3
4
5
6
7
8

SP_EXECUTESQL 支持内嵌参数：

因为参数是运行时传递进去SQL的，而不是编译时传递进去的，传递的参数是什么就按照什么执行，参数本身不参与编译




所谓的参数化SQL，就是用变量当做占位符，通过 EXEC sp_executesql执行的时候将参数传递进去SQL中，在需要填入数值或数据的地方，使用参数 (Parameter) 来给值，这样的话，

第一，既能避免第一种写法中的SQL注入问题（包括转移符的处理），
　　　因为参数是运行时传递进去SQL的，而不是编译时传递进去的，传递的参数是什么就按照什么执行，参数本身不参与编译
第二，保证执行计划的重用，因为使用占位符来拼凑SQL的，SQL参数的值不同并导致最终执行的SQL文本不同
　　　同上面，参数本身不参与编译，如果查询条件一样（SQL语句就一样），而参数不一样，并不会影响要编译的SQL文本信息

缺点，1,对于这种方式，也有一点不好的地方，就是拼凑的字符串处理过程中，调试具体的SQL语句的时候，参数是直接拼凑在SQL文本中的，不能直接执行，要手动将占位参数替换成具体的参数值