CREATE PROCEDURE cases_select_dynamic
DECLARE @sql varchar(max)
SET @sql='SELECT * FROM cases WHERE
status = ''' + CONVERT(VARCHAR(100),@status) + '''
order by ' + @sortBy
SELECT * FROM cases WHERE
status = @status
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
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
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.