EXECUTE sp_executesql

Executes a Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters.

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.


sp_executesql supports the setting of parameter values separately from the Transact-SQL string as shown in the following example.

DECLARE @employee_id INT

-- Here @employee_id  is IDENTITY Key of tbl_employee

DECLARE @SQLString nvarchar(500);

DECLARE @ParmDefinition nvarchar(500);

      

SET @employee_id = 0

 


BEGIN

SET @SQLString =

        N'INSERT INTO tbl_employee'

        (

          employee_name ,

          employee_add1 ,

          employee_add2

       )

       VALUES

       (

       @employee_name ,

       @employee_add1 ,

       @employee_add2

       )

SELECT @employee_id = SCOPE_IDENTITY()';

SET @ParmDefinition = N'@employee_name varchar(50) , @employee_add1 varchar(50),

@employee_add2 varchar(50),@employee_id INT OUTPUT ';

EXECUTE sp_executesql @SQLString, @ParmDefinition,

                      @employee_name = @employee_name , @employee_add1 = @employee_add1 , @employee_add2 = @employee_add2 , @employee_id =@employee_id OUTPUT;

END