How to Use sp_ExecuteSQL T-SQL Stored Procedure with Input and Output Parameters

sp_ExecuteSQL is a T-SQL system stored procedure that can be used to execute a dynamically built t-sql statement or a t-sql batch in SQL Server.
Of course this dynamically built t-sql statement or sql code can contain input / output parameters.

Note that you must considered the sql injection possibility or the code break down possibility if sql developers or database administrators are using sp_executesql in their t-sql codes.

A sample sp_ExecuteSQL t-sql call can be as follows. The output of the below sp_executesql statement will be a list of returned rows from Employees database table.


EXECUTE sp_executesql N'SELECT TOP 10 * FROM Employees' -- Select

EXEC GetUserByEmail N'kodyaz@kodyaz.com' -- Stored Procedure

Code

SQL developers can create the @stmt argument dynamically and call a SQL stored procedure using parameters as follows:


DECLARE @sql nvarchar(max)

DECLARE @email nvarchar(50)

SET @email = N'kodyaz@kodyaz.com'

SET @sql = 'EXEC GetUserByEmail N''' + @email + ''''

--SELECT @sql 

EXECUTE sp_executesql @sql

Code

Building the final t-sql code or final stored procedure call by adding the parameter values into the statement is easy but is not a good way of using sp_ExecuteSQL SQL procedure.
The preferred method for using sp_executesql with parameters should be using the @params argument which takes place in the sp_executesql syntax.

SQL Server sp_ExecuteSQL syntax

Below is the t-sql syntax of sp_executesql system stored procedure.
In the sp_executesql transact-sql syntax :
@stmt is the nvarchar() variable input string which identifies the t-sql statement.
@params is the nvarchar() parameter declaration string which is consists of a list of parameters and parameter data type declarations.
Following the parameter declaration string or @params sp_executesql parameter, the parameter values are passes to the sp_executesql t-sql command. Each parameter value is passed by parameter name and parameter value seperating each parameter set using comma (,)

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

Sample sp_executesql code using parameter.


DECLARE @sql nvarchar(max)

DECLARE @email_input nvarchar(50)

SET @email_input = N'kodyaz@kodyaz.com'

SET @sql = 'EXEC GetUserByEmail @email'

EXECUTE sp_executesql @sql, N'@email nvarchar(50)', @email = @email_input

Code

sp_ExecuteSQL Output Parameters syntax in T-SQL

We can define sp_ExecuteSQL parameters that gets output values from the sql stored procedures, and return output values as variables from the execution of a t-sql procedure.
The trick for using sp_ExecuteSQL OUTPUT Parameter in a SQL Server sp_executesql t-sql statement is declaring the out parameter with OUTPUT hint in the stored procedure parameter definition part. And setting the parameter value following the OUTPUT hint.
Here is a few sample sp_executesql sql codes.


DECLARE @SQL_String NVARCHAR(max)

DECLARE @Parameter_Definition NVARCHAR(max)



SET @SQL_String = N'

  SELECT * FROM dbo.Employees;

  SELECT 

    @Email_out = Email

  FROM dbo.Employees

  WHERE 

    EmployeeId = @EmployeeId_input'



SET @Parameter_Definition = N'

  @EmployeeId_input uniqueidentifier, 

  @Email_out nvarchar(50) OUTPUT'



DECLARE @EmployeeId uniqueidentifier

DECLARE @Email nvarchar(50)



SET @EmployeeId = '3E8E578C-6810-48BD-AADB-620EDECF988C'



EXECUTE sp_executesql @SQL_String,
 
  @Parameter_Definition, 

  @EmployeeId_input = @EmployeeId,
 
  @Email_out = @Email OUTPUT



SELECT @Email as Email

Code

Here is an other sample t-sql code where OUTPUT parameters are used in the sp_executesql statement.


DECLARE @SQL_String NVARCHAR(max)

DECLARE @Parameter_Definition NVARCHAR(max)



SET @SQL_String = N'EXEC GetEmail @EmployeeId_input, @Email_out OUTPUT'



SET @Parameter_Definition = N'

  @EmployeeId_input uniqueidentifier, 

  @Email_out nvarchar(50) OUTPUT'



DECLARE @EmployeeId uniqueidentifier

DECLARE @Email nvarchar(50)



SET @EmployeeId = '00FCEFA4-BF81-4674-81C1-C2DE86F0C5F6'



EXECUTE sp_executesql @SQL_String, 

  @Parameter_Definition, 

  @EmployeeId_input = @EmployeeId,
 
  @Email_out = @Email OUTPUT



SELECT @Email as Email

Code

Another sample for sp_executesql where a stored procedure is called with proc parameter names are defined in the sp_executesql statement.

Assume that you have created the following sample stored procedure.


CREATE PROC GetEmail2

(

  @EmployeeId uniqueidentifier,

  @Email nvarchar(50) OUTPUT

)

AS



SELECT * FROM dbo.Employees;



SELECT
 
  @Email = Email

FROM dbo.Employees

WHERE 

  EmployeeId = @EmployeeId;

GO

Code

Now the following transact-sql sp_executesql command can be executed as follows:


DECLARE @SQL_String NVARCHAR(max)

DECLARE @Parameter_Definition NVARCHAR(max)



SET @SQL_String = N'

  EXEC GetEmail2 @EmployeeId = @EmployeeId_input, @Email = @Email_out OUTPUT

'



SET @Parameter_Definition = N'

  @EmployeeId_input uniqueidentifier, 

  @Email_out nvarchar(50) OUTPUT'



DECLARE @EmployeeId uniqueidentifier

DECLARE @Email nvarchar(50)



SET @EmployeeId = '997B3351-F876-414B-9C63-B90EC967B69B'



EXECUTE sp_executesql @SQL_String, @Parameter_Definition, @EmployeeId_input = @EmployeeId, @Email_out = @Email OUTPUT



SELECT @Email as Email

Code

Developers can download sample t-sql codes with sp_executesql used in this article from T-SQL sp_ExecuteSQL Samples.
The sample codes includes sample database table creation, populating sql table with sample data and sample stored procedures create scripts.