EXEC vs sp_executesql

So you need dynamic SQL, not the best thing to do, but sometimes unavoidable.

What's the best way of writing it?

Can it execute faster? safer?

SQL provides two methods for executing dynamic SQL, the first and oldest is EXECUTE (or exec) and the second is sp_executesql, which came not much later.

If you have a query without changing or user parameters, exec is enough.

For example:

exec ('select * from Department');

Or you can do it with a variable:

declare @sql nvarchar(max);
set @sql = 'select * from Department';
exec (@sql)

But what happens when you need to put variables into the query, surely you can do this:

set @sql = 'select * from person where Username = ''' + @username + ''' and Password = ''' + @password + '''';
exec (@sql)

Sure, you can do it, but lets see what happens when you try this:

set @username = 'Peggy''';
set @password = ' or ''1''= ''1';
set @sql = 'select * from person where Username = ''' + @username + ''' and Password = ''' + @password + '''';
exec (@sql)

Opps.. where's did my security go?

SQL injection is still one of the more active attacks, its been known for years and still some people are not aware of it, lets see how we can solve it:

declare @username nvarchar(20);
declare @password nvarchar(20);
set @username = 'Peggy';
set @password = 'goodpassword';
set @username = REPLACE(@username,'''','''''');
set @password = REPLACE(@password,'''','''''');
declare @sql nvarchar(max)
set @sql = 'select * from person where Username = ''' + @username + ''' and Password = ''' + @password + '''';

exec (@sql)

Cool! so now no one can put a single quote to hack my application! WRONG!

Lets take a look at this:

declare @username nvarchar(20);
declare @password nvarchar(20);

set @username = 'Peggy''''''''''''''''''';
set @password = ' or (1= 1) or ''=''''''''';

set @username = REPLACE(@username,'''','''''');
set @password = REPLACE(@password,'''','''''');

set @sql = 'select * from person where Username = ''' + @username + ''' and Password = ''' + @password + '''';

exec (@sql)

Opps.... where did my security go again??

What's happening is the variables for username and password are too short to hold the last single quote, so its open, the same as with the previous attack, we can solve it, but then we'll find some other hole, so lets try to plug it once and for all.

sp_executesql to the rescue!

sp_executesql works in a similar way to the exec command but it allows you to define and pass parameters and by that eliminating the threat we've seen before.

So how would we solve the previous problem? Simple.

declare @username nvarchar(20);
declare @password nvarchar(20);

set @username = 'Peggy';
set @password = 'goodpassword'

exec sp_executesql N'select * from person where Username =@username and Password = @password', 
                    N'@username nvarchar(20), @password nvarchar(20)', @username,@password;

So what else can sp_executesql do for us?

It can speed things up by allowing execution plans to be cached.
Execution plans are the commands the sql server needs to execute so it can return the results you're asking for, each query is compiled into an execution plan and then saved in memory to save time when the query is executed again, but because its a hash, every little change will cause the server to recompile the query into another execution plan, by taking out the parameters the query stays the same for different inputs, increasing efficiency of your queries.

What else can these methods do for me?

You can insert into a table query results from other queries

With exec:

declare  @serverinfo table
(
    attribute_id int,
    attribute_name nvarchar(255),
    attribute_value nvarchar(255)
)
insert @serverinfo
exec sp_server_info

select *,'more..' from @serverinfo

with sp_executesql:

declare  @serverinfo table
(
    attribute_id int,
    attribute_name nvarchar(255),
    attribute_value nvarchar(255)
)
insert @serverinfo
exec sp_executesql N'sp_server_info'

select *,'more..' from @serverinfo

By far the best and oldest article on dynamic queries is http://www.sommarskog.se/dynamic_sql.html.

And if you're interested in further reading about execution plans:
http://www.simple-talk.com/sql/performance/execution-plan-basics/ Tags: exec, sp_executsql