exec sp_executesql 用法

DECLARE

@IntVariable int ;

DECLARE

@SQLString nvarchar ( 500 );

DECLARE

@ParmDefinition nvarchar ( 500 );

/* Build the SQL string one time.*/

SET

@SQLString =

N

'SELECT * 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

FROM AdventureWorks.HumanResources.Employee

WHERE ManagerID = @level'

@SQLString = N 'SELECT @max_titleOUT = max(Title) ;

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 ;