SQL Stored Procedure: Conditional Return

Question

Hi I want to create a simple stored proecudre which does the following:

Psudocode

@tempSelect = "SELECT * FROM Table"

if (@Param is NULL) then
 exec @tempSelect
else
 exec @tempSelect + ' WHERE id = ' + @Param + '

Is this method efficent? Thank you.

Answer 1

Try

select *
from table
where id=isnull(@param, id)

Answer 2

Select * from Table Where (ID = @Param or @Param is null)

Or

Select * from Table Where ID=Coalesce(@Param, ID)

[And if you are aiming for efficiency, replace * with the specific field you want to return.]

Answer 3

Yes - I certainly see nothing wrong with it. You could make it even simpler though:

Set NOCOUNT On;
if (@Param is NULL) 
   Select * From Table;
else 
   Select * From Table Where (ID=@Param);

Note: I'd probably spell out the fields, though.

Answer 4

Depending on the case, I would probably use dynamic SQL.

However you need to remember about SQL injection in case @param originates from a user, thats why you should never add a parameter directly to your sql.

In t-sql it would look something like (out of my head and untested ;):

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = N'
  SELECT ...
  FROM   table t
  WHERE  1 = 1' (

IF(@param IS NOT NULL)
  SET @SQL = @SQL + '
    AND t.id = @id'

... possibly more things added to the query ...

EXEC sp_executesql 
   @SQL
, '@id AS INT'
,  @id = @Param

By doing this, you will get an optimized query plan for each case (and by using sp_executesql, the query cache will be used as well)

I would especially avoid the OR solution, if you check the query plans generated with the OR compared to one without, you will understand why.