执行带参数的SQL, sp_executesql

Syntax

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

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;

输出参数

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;