Wednesday, May 10, 2006

SQL: Dynamic SQL Avoidance

I have several stored procs that take a flag that tells them wether to show expired records or not and usually looks something like this:

DECLARE @ShowExpired tinyint
SET @ShowExpired = 1

declare @sql varchar(1000)
set @sql = 'SELECT * FROM TABLE'
IF @ShowExpired = 1
set @sql = @sql + ' WHERE eff_end_dt is NULL"
execute (@sql)

While this works fine if I loose the advantages of stored procs and if I rename a table and recreate the procedure (I periodically recreate all procedures to test this) then I will not see any error.

Instead I came up with this:

DECLARE @ShowExpired tinyint
SET @ShowExpired = 1

select * from Table
where ((@ShowExpired=1) or (@ShowExpired=0 and eff_end_dt is null))

basically all the is happening if @ShowExpired=1 then everything gets returned. But if @ShowExpired=0 then only records that have a null end date are returned.


Post a Comment

<< Home