How to use 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 and is very useful in SQL development.

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

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.

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 
EmployeeId=@EId'
 
SET @ParmDefinition = N'@EId INT, @Title NVARCHAR(30) OUTPUT, @BirthDate 
DATETIME OUTPUT'
 
EXEC sp_executesql @query,   
     @ParmDefinition,
     @EId=5,
     @Title=@Title OUTPUT, 
     @BirthDate=@BirthDate 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:

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 it’s output parameter.

See the Products table of Northwind database.

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.

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 ProductID=@ProductId

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,
     @ProductName=@ProductName OUTPUT, 
     @UnitPrice=@UnitPrice OUTPUT,
     @QuantityPerUnit=@QuantityPerUnit OUTPUT
 
SELECT @ProductName AS 'ProductName', @UnitPrice AS 'UnitePrice', 
@QuantityPerUnit AS 'QuantityPerUnit'

The Output is shown in the image below:

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.