CODEWITHSUNDEEP
sqlstored-proceduresbranch
DavidS
DavidS

Reputation: 259

SQL Stored Procedure: Conditional Return

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.

Upvotes: 2

Views: 1962

Answers (5)

Salassa
Salassa

Reputation: 1

Try this code:

CREATE PROCEDURE [dbo].[ProcedureName]
@Param varchar(50)

AS
BEGIN

declare @tempSelect nvarchar(max)  

SET NOCOUNT ON;

set @tempSelect = 'SELECT Col1, Col2 FROM Table where Col1 <> '' '

         if @Param <> '' 
    begin
    set @resultSet = @resultSet +  ''' and Col1='''+@Param1 
    end


EXEC(@resultSet)

END

Upvotes: 0

Brimstedt
Brimstedt

Reputation: 3140

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.

Upvotes: 0

CJM
CJM

Reputation: 12016

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.]

Upvotes: 1

Mark Brittingham
Mark Brittingham

Reputation: 28865

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.

Upvotes: 0

John MacIntyre
John MacIntyre

Reputation: 13021

Try 

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

Upvotes: 10

Related Questions