一、使用EXEC执行存储过程
例如存储过名为:myprocedure
- use
AdventureWorks -
- create
procedure myprocedure @city varchar(20) -
- as
-
- begin
-
-
select * from Person.Address -
- end
- exec
myprocedure @city = 'Bothell' -
- --或
-
- exec
myprocedure 'Bothell'
二、使用EXEC执行动态的SQL语句
注意:动态的sql必须包含于圆括号内如:
- exec
('select * from mytable')
使用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
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);
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
当@i = 10248, 10249, 10250要生成3个执行计划。
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 --';