SQL Stored Proc With Dynamic Where

A Learning Opportunity

Today I had a lovely learning opportunity when I discovered a stored procedure that was not quite safe.  It used parameters, and the C# code sent them over using proper parameters.  The problem came to how the entire SQL statement was being built and executed.

In fact, you could say it was more about how the dynamic WHERE clause was being built.

Naturally I went on a quick quest to find a better way to do this and be secure.  This MSDN article really helped.  It gave me enough knowledge to write an example against a Northwind database.

Our Example

What we are going to do is create a stored procedure that will take two parameters.  These parameters might also be NULL, so we need to take that into account.  We will use the EmpoyeeID and Region to query our Employees table.

-- =============================================
-- Author:	SheldonS
-- Create date: 1/15/2016
-- Description:	An example of writing a stored proc with
--		dynamic WHERE clause
-- =============================================
CREATE PROCEDURE sst_SheldonS_Test
	-- Add the parameters for the stored procedure here
	@nEmpID AS INT,
	@sRegion AS NVARCHAR(25)
AS

Using the sp_executesql procedure we will need to look at this a little differently.  Normally you know you need something to hold your SELECT clause, and something to hold your WHERE clause, but you will also need something to hold your parameter definitions.  For us, our SELECT clause is going to be very straight forward, we want to select everything in the Employees table.

BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	DECLARE @SQLSelect AS NVARCHAR(MAX);	--Holds the select clause
	DECLARE @SQLWhere AS NVARCHAR(MAX);	--Holds the where clause
	DECLARE @PDef AS NVARCHAR(500);		--Holds definitions of parameters used in the where clause

	--SELECT
	SET @SQLSelect = N'SELECT * FROM Employees';

Here comes the fun part, creating our dynamic WHERE clause.  Basically we are going to check each parameter to see if it has a value, if so we will add a WHERE condition to look for that value.  If it is NULL or doesn’t have a value we will still add a WHERE condition but to look for something else like a default value.

When we do this we again have to use parameters.  So we will create new ones in our WHERE conditions.  This means we have to define those new parameters in our definitions parameter.  I know, it sounds confusing, but really it is quite simple.

	--Build WHERE
	IF(@sRegion <> '')
	BEGIN
		SET @SQLWhere = N' WHERE Region = @Reg';
	END
	ELSE
	BEGIN
		SET @sRegion = ISNULL(@sRegion, '');	--Take into account the @sRegion param could be null
		SET @SQLWhere = N' WHERE @Reg = @Reg';
	END
	SET @PDef = N'@Reg NVARCHAR(15)';	--Add @Reg definition

	IF(@nEmpID > 0)
	BEGIN
		SET @SQLWhere = @SQLWhere + N' AND EmployeeID = @ID';
	END
	ELSE
	BEGIN
		SET @nEmpID = ISNULL(@nEmpID, 0);	--Take into account the @nEmpID param could be null
		SET @SQLWhere = @SQLWhere + N' AND @ID = @ID';
	END
	SET @PDef = @PDef + N', @ID INT'	--Add @ID definition

Our next step is really simple.  We need to stitch our SELECT and WHERE clauses together.

	--Build the actual command
	DECLARE @SQL AS NVARCHAR(MAX);
	SET @SQL = @SQLSelect + @SQLWhere;

Finally we need to execute our SQL.  The sp_executesql procedure is looking for our SQL command, our parameter definitions, and finally the values for the parameters we defined.

	--Execute the command with parameters
	EXECUTE sp_executesql @SQL, @PDef,	@Reg = @sRegion,
										@ID = @nEmpID;

Lets run some queries and see what we get!

As you can see, we get the expected data.  This works even when I try to simulate an SQL Injection attack.  I hope you found this useful and maybe even saved you some pain.

Advertisements

About SheldonS

Web developer for over 15 years mainly with Microsoft technologies from classic ASP to .NET 4. Husband, father, and aspiring amateur photographer.

Posted on January 15, 2016, in SQL and tagged . Bookmark the permalink. 2 Comments.

  1. Isn’t there a much simpler solution without using dynamic SQL though?

  2. There might be for something this simple, but for what I was actually working on there were a lot of parameters sent to the stored procedure and used to construct the WHERE clause.

    Further playing around with it you don’t really need to set the else, except maybe the first one so you actually have the WHERE clause. You just need to set the parameter you are sending, like this example. This will result in a smaller SQL command. As long as your first check creates the WHERE clause you don’t have to keep checking to see if the WHERE clause is already started.

    IF(@nEmpID > 0)
    BEGIN
    SET @SQLWhere = @SQLWhere + N’ AND EmployeeID = @ID’;
    END
    SET @PDef = @PDef + N’, @ID INT’ –Add @ID definition

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: