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
– Martin Smith, Commented Aug 26, 2011 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
– TimB, Commented Aug 26, 2011 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
– TimB, Commented Aug 26, 2011 at 20:25
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
– marc_s, Commented Aug 26, 2011 at 20:30
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
– Aaron Bertrand, Commented Aug 27, 2011 at 14:30

Community · Accepted Answer · 2017-05-23 12:19:53Z

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.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related