Using sp_executesql to Execute Dynamic Statements and Stored Procedures

The sp_executesql statement is used to execute dynamically built T-SQL Statements and stored procedures. It can have both input and output parameters while executing statements and is very useful in SQL development.

Let us see how to use sp_executesql statement in SQL Server.

Note – For the examples I am using the help of Northwind Database.

Executing Dynamically Built Statement and Get the Values using Output Parameter

Let us see how to execute dynamic statements with sp_executesql. Here I am taking the Employees table of Northwind Database.

northwind employees table

In the below SQL Code I am executing a dynamic T-SQL Statement. In this I am using sp_executesql to fetch me the Title and Birthdate of the employee whose Id is ‘5’.

DECLARE @Query                  NVARCHAR(250)
DECLARE @ParmDefinition         NVARCHAR(250)
DECLARE @Title                  NVARCHAR(30)
DECLARE @BirthDate              DATETIME

Set @Query = 'Select @Title=Title,@BirthDate=BirthDate From EMPLOYEES WHERE [email protected]'
SET @ParmDefinition = N'@EId INT, @Title NVARCHAR(30) OUTPUT, @BirthDate DATETIME OUTPUT'
EXEC sp_executesql @query,   
     @ParmDefinition,
     @EId=5,
     @[email protected] OUTPUT, 
     @[email protected] OUTPUT
SELECT @Title AS 'Title', @BirthDate AS 'BirthDate'

I am providing the employee id with ‘@Eid’ parameter and also using ‘2 output’ parameters ‘@Title’ and ‘@BirthDate’. Through these output parameters I am getting back the ‘Title’ and ‘Birthdate’ of the employee with id 5.

The Output which I get is shown in the image below:

execute dynamic statement with sq_executesql

Executing A Stored Procedure and Get Back its Output Parameter Values

The sq_executesql can also be used to execute a stored procedure and get back the values of its output parameter.

Now take the Products table of Northwind database.

northwind products table

I created a stored procedure ‘GetProductDetails’, the work of this SP is to return the ‘ProductName’, ‘UnitPrice’ and ‘QuantityPerUnit’ through output parameters. The ‘ProductId’ is provided through the input parameter to this SP.

The Stored Procedure is given below:

Create Procedure GetProductDetails
	@ProductId          INT,
	@ProductName        VARCHAR(100)     OUTPUT,
	@UnitPrice          MONEY            OUTPUT,
	@QuantityPerUnit    VARCHAR(20)      OUTPUT
AS
	SELECT @ProductName=ProductName,@UnitPrice=UnitPrice,@QuantityPerUnit=QuantityPerUnit FROM Products WHERE [email protected]

Now I use the Power of ‘sp_executesql’ to execute the ‘GetProductDetails’ SP, and get me the values – ‘ProductName’, ‘UnitPrice’ and ‘QuantityPerUnit’ of product whose ProductId is 11.

The SQL Code of this is given below:

DECLARE @Query                  NVARCHAR(250)
DECLARE @ParmDefinition         NVARCHAR(250)
DECLARE @ProductName            VARCHAR(100)
DECLARE @UnitPrice              MONEY
DECLARE @QuantityPerUnit        VARCHAR(20)

Set @Query = 'Exec GetProductDetails @ProductId, @ProductName OUTPUT, @UnitPrice OUTPUT, @QuantityPerUnit OUTPUT'
SET @ParmDefinition = '@ProductId INT, @ProductName VARCHAR(100) OUTPUT, @UnitPrice MONEY OUTPUT, @QuantityPerUnit VARCHAR(20) OUTPUT'
EXEC sp_executesql @query,   
     @ParmDefinition,
     @ProductId=11,
     @[email protected] OUTPUT, 
     @[email protected] OUTPUT,
	 @[email protected] OUTPUT

SELECT @ProductName AS 'ProductName', @UnitPrice AS 'UnitePrice', @QuantityPerUnit AS 'QuantityPerUnit'

The Output is shown in the image below:

Execute Stored Procedure with sq_executesql

Conclusion
I find sp_executesql statement very using and it helps me in building great SQL codes. Use it whenever you find it useful in your SQL Server project.

Share this article -

yogihosting

ABOUT THE AUTHOR

This article has been written by the Technical Staff of YogiHosting. Check out other articles on “WordPress, SEO, jQuery, HTML” and more.