THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Aaron Bertrand

Aaron is a Product Evangelist for SentryOne, makers of Plan Explorer and a performance monitoring and event management platform for the Microsoft Data Platform and VMware. He has been blogging here at since 2006, focusing on manageability, performance, and new features, and also blogs at and; has been a Microsoft MVP since 1997; tweets as @AaronBertrand; and speaks frequently at major conferences, user group meetings, and SQL Saturday events worldwide.

Bad Habits to Kick : Using EXEC() instead of sp_executesql

This blog has moved! You can find this content at the following new location at

Published Saturday, September 17, 2011 1:50 PM by AaronBertrand



giantism_strikes said:

Since this is doing a soft parse and the query plan will be reused, is there a performance gain between using a stored procedure and dynamic, parameterized SQL?

September 17, 2011 2:56 PM

AaronBertrand said:

I don't believe so, but I would still opt for stored procedures for other reasons (manageability, maintenance, modularization, consistency).

September 17, 2011 4:48 PM

jchang said:

my preference is to employ stored procedures for the reason AB cited

and then within a stored procedure, employ sp_executesql to handle multiple optional search arguments


instead of this code


FROM TableA JOIN TableB etc

WHERE (@P1 IS NULL OR Col1 = @P1)

 AND (@P2 IS NULL OR Col2 = @P2)


use sp_executesql instead

DECLARE @SQL nvarchar(xxx), @b tinyint = 0

SET @SQL = N'SELECT xx FROM TableA JOIN TableB etc WHERE 1=1 '


SET @SQL = @SQL + N' AND Col1 = @P1 '


SET @SQL = @SQL + N' AND Col2 = @P2 '

I would just declare and apply all the parameters, regardless if they are null

as in

exec sp_executesql @SQL, N'@P1 type, @P2 type, etc', @P1, @P2, etc

September 18, 2011 12:22 PM

Jack Corbett said:

I agree with Aaron and Joe about still using SP's and the method Joe espouses for multiple optional search arguments.  It can get a bit ugly looking with a lot of parameters, but is still easier to maintain and expand.  I have blogged about it as well.

September 23, 2011 10:40 AM

krismaly said:

AB said right.

Always use Stored Procs let it be simple or complicated since Maintenance, Manageability, Modularity, and other uses

May 27, 2015 9:22 AM

Sandy said:

I am sorry.

I don't understand. why the latter won't execute ?

Is it not replacing @foo ?

It should execute like the former one but defineltely, I am missing something. Please help.

March 24, 2016 6:00 AM

AaronBertrand said:

@Sandy @foo remains a string throughout. The difference is the first one ends up as this:

   SELECT foo FROM dbo.table WHERE foo = 'string'; DROP TABLE dbo.table; --''

Which will obviously try to execute both commands (and will succeed if the user has permissions, which is all too common with dynamic SQL).

When you pass @foo as a parameter, no, it does not simply replace the string and execute. SQL Server builds a plan where the parameter is a token and it is passed to the engine to execute.  So SQL Server literally looks for a string in dbo.table that matches the entire parameter value. It is much like this:

   DECLARE @foo NVARCHAR(32) = N'string''; DROP TABLE dbo.table; --''';

   SELECT foo FROM dbo.table WHERE foo = @foo;

No string replacement ever happens here.

March 30, 2016 9:49 AM

Juan Hernandez said:

Why has Microsoft enchanced EXEC() instruccion in SQL 2012  (see WITH RESULT SET) and not done the same with sp_executsql ??

April 20, 2016 4:22 PM
New Comments to this post are disabled

About AaronBertrand

...about me...

This Blog



Privacy Statement