CODEWITHSUNDEEP
sql-server-2008stored-procedures
TimB
TimB

Reputation: 591

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

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

Upvotes: 3

Views: 2465

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

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:

  1. sp_executesql is more likely to reuse query plans (see Dynamic SQL - EXEC(@SQL) versus EXEC SP_EXECUTESQL(@SQL));

  2. it is much easier to pass strongly-typed parameters into sp_executesql (thwarting SQL injection better than concatenating a string); and,

  3. 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.

Upvotes: 3

Related Questions