SQL EXEC用法总结 |
您所在的位置:网站首页 › sql中sp是什么意思 › SQL EXEC用法总结 |
文章来自:博客园-易尔购 一、使用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); GO2.园括号内部能使用函数或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 --';
|
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |