[Tuning] Getting rid of Dynamic SQL

We've all done it. There's times when you just need to write something quick and dirty that deals with a simple problem. And there doesn't seem to be an easy way to make it proper SQL, so you write some dynamic SQL instead.

CREATE PROCEDURE cases_select_dynamic
@sortBy varchar(100),
  @status Int
AS
BEGIN
IF len(@sortBy)>0
BEGIN
 DECLARE @sql varchar(max)
 SET @sql='SELECT * FROM cases WHERE 
    status = ''' + CONVERT(VARCHAR(100),@status) + '''
 order by ' + @sortBy
 EXEC (@sql)
END
ELSE
BEGIN
 SELECT * FROM cases WHERE 
    status = @status
END
END

Fortunately, there are people out there who don't particularly like dynamic SQL. Fortunately for us, because they've found ways around it. Instead of the above, do something like

CREATE PROCEDURE cases_select_nondynamic
  @sortBy varchar(100),
  @status Int
as 
BEGIN
 SELECT * 
 FROM cases
 WHERE  status = @status
 order by CASE @sortBy WHEN 'finished' THEN finished ELSE NULL END, 
   CASE @sortBy WHEN 'name' THEN name ELSE NULL END, 
   CASE @sortBy WHEN 'lead' THEN lead ELSE NULL END
END

Which does the same thing. A little more work, but you gain several benefits.

• Dynamic SQL runs under the users context, so they need perms to the tables. Nondynamic means you just grant normal SP rights.
  
• Cached plans. 2000/2005 are good about reusing the cached plan - if it's IDENTICAL. Unless people are always running the exact same query, parameters and all, it won't cache. Nondynamic gets cached once and used for all parameters.
  
• Faster. In my case, running the exact same parameters for both versions resulted in the dynamic version having a 89% Query cost (relative to the batch), while the nondynamic version was a mere 11%.
  

So go out there and convert 1 SP today to be nondynamic. Read Erland's page, he's got a lot of examples and a lot of thought in there.

http://www.sommarskog.se/dynamic_sql.html . Erland is a SQL god.