SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014



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'phalberg@adatum.com' -- Stored Procedure

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'phalberg@adatum.com'
SET @sql = 'EXEC GetUserByEmail N''' + @email + ''''
--SELECT @sql
EXECUTE sp_executesql @sql

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'phalberg@adatum.com'
SET @sql = 'EXEC GetUserByEmail @email'
EXECUTE sp_executesql @sql, N'@email nvarchar(50)', @email = @email_input


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

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

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

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

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.






Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems