Tuesday 16 June 2015

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