Anyone who knows me, or has worked with me, knows that I am a proponent of using stored procedures for all database access.  I believe that using stored procedures makes your database more secure and makes it simpler to maintain a well performing system.  One area where stored procedures are more difficult to work with than building queries in the GUI or business layer are with dynamic search queries.  Here are some traditional issues with dynamic search in stored procedures:

1. If you use traditional IF, ELSE statements to build the procedure you get a long and hard read procedure, and you are less likely to get plan re-use.
2. If you try tricks like WHERE LastName = IsNull(@LastName, LastName) and FirstName = IsNull(@FirstName, FirstName) you can get plan re-use, but the plan used may not be, and many times is not, the best plan to use.
3. If you use dynamic SQL using the EXEC (@sql) syntax you do not get plan re-use, you open yourself up for SQL Injection, and, prior to SQL Server 2005, you had to grant access to the objects used in the query defeating part of the reason for using stored procedures in the first place.
4. If you use dynamic SQL using sp_executsql and parameters you are more likely to get plan re-use, you are safe from sql injection, but, pre-2005, you still needed to grant access to the queried objects.
5. Either dynamic SQL option means creating a large string of SQL and concatenating it, so it can be and, in my opinion is, a pain to read and a pain to make sure you have all your syntax right.

See Erland Sommarskog's excellent articles, The curse and blessings of dynamic SQL and Dynamic Search Conditions, for more details.

Early in my career, when working with SQL Server 7 and 2000 I tended to use option 1, sometimes with temporary tables, then I moved to option 2.  I never used dynamic SQL because I did not want to grant select access to the tables being queried.  I sacrificed performance for security and counted on ownership chaining to handle access to the underlying tables.  With the advent of SQL Server 2005 and the EXECUTE AS I have moved to option 4, dynamic SQL using sp_executesql and parameters as I believe it gives me the best of both worlds.

How’s it work

Essentially you create the stored procedure and add the WITH EXECUTE AS Caller/Owner/Self/’user name’/’login name’ (see the Books on Line entry for EXECUTE AS for more details) and this changes the context in which the code within the procedure is run.  So you can create a user (SelectAll) in the database that has select rights on all the tables and then no matter who calls the stored procedure the procedure will run correctly.  If you choose to use EXECUTE AS OWNER then the procedure executes in the security context of the Owner of the procedure so you can simulate ownership chaining. 

Example

Security

A post like this wouldn’t be complete without at least a simple example.  I will be using the AdventureWorks database (get it at CodePlex, I’m still using the 2005 version) with some added data (I used RedGate SQLDataGenerator).  All the code to run the examples is available for download here.

First you need to create a user with limited persmissions:

Use AdventureWorks;

Go

Create User DynamicSQLTest without login;

Notice that I used the Without Login syntax so I did not need to create a login as well.  This is because I will also be using EXECUTE AS before running the stored procedure to change my execution context to this limited rights user instead of creating a new connection with a limited rights login.  Next you need to create the stored procedure.  I’m going to start with a “normal” stored procedure using Option 2 from above, because I also want to demonstrate the difference in performance.  Here’s the procedure:

Use AdventureWorks;
GO

IF OBJECT_ID('dbo.FindPhoneByName', N'P') Is Not Null
Begin
        Drop Procedure dbo.FindPhoneByName;
End;

Go

CREATE PROCEDURE dbo.FindPhoneByName
(
@LastName nvarchar(50) = null,
@FirstName nvarchar(50) = null
    )
AS

BEGIN
 SET NOCOUNT ON;

Select
Title,
FirstName,
MiddleName,
LastName,
Suffix,
Phone  
From
Person.Contact
Where 
LastName Like IsNull(@LastName, LastName) + N'%' And 
FirstName Like IsNull(@FirstName, FirstName) + N'%';

Return;         
END
GO

This procedure is pretty self explanatory.  Now we need to give the limited rights use, DynamicSQLTest, execute rights on our procedure:

Use AdventureWorks;

Go

Grant Exec on dbo.FindPhoneByName to DynamicSQLTest;

To test the security and performance of the stored procedure we are going to execute it 3 times with a dbo user and then repeat as the limited rights user, DynamicSQLTest.  Here is what I used:

Exec dbo.FindPhoneByName @FirstName = 'J', @LastName = 'A';

Go

Exec dbo.FindPhoneByName @FirstName = 'J';

Go

Exec dbo.FindPhoneByName @LastName = 'A';

Go

Then execute the same 3 calls, but run this first to change the security context:

Execute AS User = 'DynamicSQLTest';

Go

If you are running the code in the same SSMS session be sure to issue the REVERT command to return to your original security context.

The stored procedure calls should run successfully for both users and should produce the same results and performance for both users.  Now we’ll ALTER the procedure to use dynamic SQL:

Alter PROCEDURE dbo.FindPhoneByName
(
@LastName nvarchar(50) = null,
@FirstName nvarchar(50) = null
    )
AS

BEGIN
    SET NOCOUNT ON;

Declare @sql_cmd nvarchar(2000),
@select nvarchar(1000),
@where nvarchar(1000),
@parameters nvarchar(1000);

Set @parameters = N'@FirstName nvarchar(50), @LastName nvarchar(50)';

Set @select = N'Select
                     Title,
                     FirstName,
                     MiddleName,
                     LastName,
                     Suffix,
                     Phone  
                From
                    Person.Contact';

Set @where = N' Where 1=1 '                

If @LastName is not null
Begin
            Set @where = @where + N' And LastName Like @LastName + N''%'' ';
End;

If @FirstName is not null     
Begin
            Set @where = @where + N' And FirstName Like @FirstName + N''%''';
End;

Set @sql_cmd = @select + @where;

Exec sys.sp_executesql @sql_cmd, @parameters, @LastName = @LastName, @FirstName = @FirstName;

Return;         
END

Now when you run our examples, you’ll see that it runs successfully under your original security context, but you receive an error when you run it as the limited rights user:

Msg 229, Level 14, State 5, Line 1

The SELECT permission was denied on the object 'Contact', database 'AdventureWorks', schema 'Person'.

This is because the execution context changed and ownership chaining no longer applies.  To get the Dynamic SQL Stored procedure to work add WITH EXECUTE AS OWNER after the parameter definition like this:

ALTER PROCEDURE dbo.FindPhoneByName
(
@LastName nvarchar(50) = null,
@FirstName nvarchar(50) = null
    )
With Execute As owner
AS

Then you can re-run the your stored procedure calls and they should work both for the dbo user and the limited rights user because the EXECUTE AS OWNER has enabled access to the tables.

Perfomance

I ran all of my examples with SET STATISTICS IO ON so I could see the results.  Here are those results (also part of the download):

Notice the reduced number of reads required by the Dynamic SQL when only 1 parameter is supplied.  This is because it is using a different query plan, while the Non-Dynamic procedure has one query plan which is not optimal when only one parameter is supplied

Conclusions

As you can see some of the limitations of Dynamic SQL have been “cured” by the advent of the EXECUTE AS clause.  This has made it simpler to use Dynamic SQL and get the performance benefits provided by getting a proper execution plan and getting plan re-use.  Again all code is available here.

Next I’ll be discussing using a Certificate to sign a stored procedure.