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

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.