SQL Server use EXEC/sp_executesql or just plain sql in stored procedure?

Question

I have a simple sproc, what is the best way to code it for best query caching/optimization?

I currently have it as follows.

ALTER PROCEDURE dbo.OccupierGet

(
@OccupierID int = 0
)

AS
/* SET NOCOUNT ON */
--Get all details that can be editted.
select TOP 1 ID,AccountNumber,FirstName,LastName,Company,Telephone,Notes,
OccupierTypeID,Address,Address2,City,Country,Telephone2,HomePhone,CellPhone,WorkPhone,Fax,EmailAddress 
from dbo.Occupier
where ID = @OccupierID

RETURN

Would it be better to build the sql query as a string and run with sp_executesql and pass the parameter? I'm asking because of query caching and the parameter I'm using.

Thank you! Tim

Both will be stored as parameterised queries. What difference are you thinking it will make? — Martin Smith, Aug 26 '11 at 20:06
That's exactly what I was wondering... I've tried to find this on the internet because somewhere I got the feeling that I had to use sp_executesql for best practice but I really didn't want to believe it. This confirms my hopes, thanks! — TimB, Aug 26 '11 at 20:19
BTW, I am working on my first program for a company on my own and I'm trying to learn every piece of "best practice" stuff I can so I won't look at my code later and regret what I did... — TimB, Aug 26 '11 at 20:25
There's no particular advantage to `sp_executesql` that I can think of. Disadvantages are that you send larger queries to the server, granting permissions and security is less straight forward and also having queries in your code base can make refactoring the database more tricky as it is less easy to see dependencies. — Martin Smith, Aug 26 '11 at 20:29
I don't see any benefit whatsoever to use `sp_executesql` in this situation. As long as you use **parametrized queries** to avoid SQL injection (and that's what you're already doing!), you're fine - just use the "plain" SQL inside your stored proc. — marc_s, Aug 26 '11 at 20:30
Wow thanks Martin and marc_s . This is exactly what I was looking for and the answers came **much** faster than I had expected. Thanks!! p.s. this is my first question on s.o., do I need to accept your answers somehow or what can I do to make you proud? =) — TimB, Aug 26 '11 at 20:38
Another way to make this specific query more predictable: don't use `TOP` without `ORDER BY`. Also why is `SET NOCOUNT ON` commented out? — Aaron Bertrand, Aug 27 '11 at 14:30
@stack.user.999 This specific query will always return only one record. The reason I had TOP 1 in there is because I saw somewhere that it might improve performance in some cases when it finds 1 row it won't continue looking for more matches. I guess it wouldn't help anything here because ID is PK..? SET NOCOUNT ON is commented because that's how VS2010 puts it by default and I have not uncommented yet. — TimB, Aug 29 '11 at 13:34

score 2 · Accepted Answer · edited May 23 '17 at 12:19

See no reason to use dynamic SQL here. When you do need to use dynamic SQL, you should consider sp_executesql higher in preference than EXEC(). There are a variety of reasons, including:

sp_executesql is more likely to reuse query plans (see Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL));
it is much easier to pass strongly-typed parameters into sp_executesql (thwarting SQL injection better than concatenating a string); and,
you can also get variables from within the dynamic SQL scope back out to the calling scope, for example:

DECLARE @i INT, @sql NVARCHAR(MAX), @dbname SYSNAME = N'model';

SET @sql = N'SELECT @i = COUNT(*) FROM ' 
    + @dbname + '.sys.tables;' 

EXEC sp_executesql @sql, N'@i INT OUTPUT', @i = @i OUTPUT;

PRINT @i;

That's not a very useful example, but it is a common problem when executing dynamic strings. But more to the point, you should only be considering dynamic SQL when you have to, not as a first resort.

SQL Server use EXEC/sp_executesql or just plain sql in stored procedure?

1 Answers1

Linked