执行带参数的SQL, sp_executesql

最新推荐文章于 2021-02-05 08:17:46 发布

weixin_34376562

最新推荐文章于 2021-02-05 08:17:46 发布

阅读量95

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/reinstallsys/archive/2009/06/15/1503743.html

本文介绍了使用sp_executesql存储过程执行动态SQL语句的方法，并通过两个实例展示了如何使用参数化查询及输出参数。

Syntax


sp_executesql [ @stmt = ] stmt [ {, [@params=] N'@parameter_name data_type [ OUT \| OUTPUT ][,...n]' } {, [ @param1 = ] 'value1' [ ,...n ] } ]

例1
DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

/* Build the SQL string one time.*/
SET @SQLString =
     N'SELECT EmployeeID, NationalIDNumber, Title, ManagerID
       FROM AdventureWorks.HumanResources.Employee
       WHERE ManagerID = @ManagerID';
SET @ParmDefinition = N'@ManagerID tinyint';
/* Execute the string with the first parameter value. */
SET @IntVariable = 197;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ManagerID = @IntVariable;
/* Execute the same string with the second parameter value. */
SET @IntVariable = 109;
EXECUTE sp_executesql @SQLString, @ParmDefinition,
                      @ManagerID = @IntVariable;

例2：传出参数
DECLARE @IntVariable int;
DECLARE @SQLString nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @max_title varchar(30);

SET @IntVariable = 197;
SET @SQLString = N'SELECT @max_titleOUT = max(Title)
FROM AdventureWorks.HumanResources.Employee
WHERE ManagerID = @level';
SET @ParmDefinition = N'@level tinyint, @max_titleOUT varchar(30) OUTPUT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @level = @IntVariable, @max_titleOUT=@max_title OUTPUT;
SELECT @max_title;

转载于:https://www.cnblogs.com/reinstallsys/archive/2009/06/15/1503743.html