Executing a dynamic T-SQL query (using sp_executesql)

Today at work I needed to make a simple stored procedure containing a simple search query with some dynamic ‘WHERE’ conditions. Since I am working with Miscrosoft SQL Server 2005 I could pick between the EXECUTE (or EXEC) command or the ‘sp_executesql’ system stored procedure. I knew that both options existed and until today I always figured they were just an alias for each other (shame on me). I finally decided to spend some time researching if there are any differences between the two commands. This is where I stumbled upon the following blog post from Pinal Dave: SQL SERVER – 2005 Comparison SP_EXECUTESQL vs EXECUTE/EXEC.

As explained by Pinal Dave you should use sp_executesql over the EXECUTE command because it accepts parameterized statements. Parameterized statements has a few big advantages (for more details please read Pinal Dave’s article):

Using parameterized statements is more secure, since you don’t have to worry about SQL injection (when implemented properly of course).
Parameterized statements give the advantage of a cached query plan, which improves performance.
Parameterized statements also improve the readability of your SQL code.

So far so good everything is clear lets forget about the EXECUTE command and start using the ‘sp_executesql’ stored procedure from now on…

I decided to write a little query using the AdventureWorks database which will fetch the employee title and name for a particular employee when a EmployeeID is supplied, or fetch the title and name for all employees if no EmployeeID is supplied. Using the syntax description on Pinal’s blog I quickly created the following query:

USE [AdventureWorks];
GO
 
DECLARE @employeeID INT; SET @employeeID = 1;
DECLARE @query NVARCHAR(MAX);
 
-- Construct the base query.
SET @query = 'SELECT E.Title, C.FirstName, C.MiddleName, C.LastName
FROM HumanResources.Employee E
	INNER JOIN Person.Contact C ON E.ContactID = C.ContactID ';
 
-- See if we need to add an condition to filter on EmployeeID
IF(@employeeID IS NOT NULL)
BEGIN
	SET @query = @query + 'WHERE E.EmployeeID = @empID';
END
 
-- Execute the parameterized dynamic query using 'sp_executesql'
EXECUTE sp_executesql @query,
	N'@empID INT',
	@empID = @employeeID;

No problems there, but what happens when I want to fetch the employees based on a supplied search string for the first- or lastname? Also no problem, the code below shows how this can be accomplished:

USE [AdventureWorks];
GO
 
DECLARE @firstName NVARCHAR(50); SET @firstName = '%G%';
DECLARE @lastName NVARCHAR(50);  SET @lastName = '%u%';
DECLARE @setWhere BIT; SET @setWhere = 1;
DECLARE @query NVARCHAR(MAX);
 
-- Construct the base query.
SET @query = 'SELECT E.Title, C.FirstName, C.MiddleName, C.LastName
FROM HumanResources.Employee E
	INNER JOIN Person.Contact C ON E.ContactID = C.ContactID ';
 
-- See if we need to add an condition to filter on the firstname.
IF(@firstName IS NOT NULL AND @firstName != '')
BEGIN
	SET @query = @query + ' WHERE C.FirstName LIKE @firstN ';
	SET @setWhere = 0;
END
 
-- See if we need to add an condition to filter on the lastname.
IF(@lastName IS NOT NULL AND @lastName != '')
BEGIN
	IF(@setWhere = 1)
	BEGIN
		SET @query = @query + ' WHERE C.LastName LIKE @lastN ';
		SET @setWhere = 0;
	END
	ELSE
	BEGIN
		SET @query = @query + ' AND C.LastName LIKE @lastN ';
	END
END
 
-- Execute the parameterized dynamic query using 'sp_executesql'
EXECUTE sp_executesql @query,
	N'@firstN NVARCHAR(50), @lastN NVARCHAR(50)',
	@firstN = @firstName, @lastN = @lastName;

The samples used in this post can probably be written in a better easier way but my goal is to illustrate the use of the ‘sp_executesql’ stored procedure to execute parameterized queries dynamically constructed inside a SQL script. Hopefully you will find this post helpful, if you have any suggestions, questions or comments be sure to leave a message.

References:

The ways of Mauri

Executing a dynamic T-SQL query (using sp_executesql)

Leave a Reply Cancel reply