Select all if parameter is null in stored procedure

Question

I want to create a procedure in SQL Server that will select and join two tables. The parameters @company, @from and @to are always set but @serie_type can be NULL. If @serie_type is not NULL i just want to include the specified types, simple AND S.Type = @serie_type, but if @serie_type is NULL i want to include all types, simple, just dont include the AND statement. My problem is that i dont know if @serie_type will be set therefore i would like o have something like this:

/* pseudocode */
??? = AND (IF @serie_type IS NOT NULL S.Type = @serie_type)

Here is a simpifyed version of procedure:

CREATE PROCEDURE Report_CompanySerie
    @company    INT,
    @serie_type INT,
    @from       DATE,
    @to         DATE
AS
BEGIN
    SELECT
        *
    FROM Company C
        JOIN Series S ON S.Company_FK = C.Id
    WHERE C.Id = @company 
        AND S.Created >= @from
        AND S.Created <= @to
/* HERE IS MY PROBLEM */        
        AND ???
END
GO

Don't want to duplicate the select becaust the real select is way bigger then this.

see this: http://stackoverflow.com/questions/2788391/optimal-search-queries/2788418#2788418 — KM., Sep 22 '11 at 15:31

score 16 · Answer 1 · answered Sep 22 '11 at 11:26

16

The common approach is:

WHERE 
C.Id = @company          
AND S.Created >= @from         
AND S.Created <= @to 
AND  (@serie_type IS NULL OR S.Type = @serie_type)

answered Sep 22 '11 at 11:26

sll

61,540
22
104
156

Its a perfact answer as check with both side. – Vimal bhatt May 14 '13 at 10:17

Preet Sangha · Accepted Answer · 2011-09-22T11:41:24.137

11

There is no need to do AND (@serie_type IS NULL OR S.Type = @serie_type) as SQL Server has a built in function to do this logic for you.

Try this:

   .
   .
   AND  S.Type = isnull( @serie_type, S.Type)

This returns

true if @serie_type is null or the result of @serie_type = S.Type if @serie_type is not null.

From the MSDN:

IsNull Replaces NULL with the specified replacement value.
ISNULL ( check_expression , replacement_value )
The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different.

edited Sep 22 '11 at 11:41

answered Sep 22 '11 at 11:27

Preet Sangha

64,563
18
145
216

1

@jeroenh : looks like you'are wrong, `ISNULL ( check_expression , replacement_value )` - "Returns the same type as check_expression." – sll Sep 22 '11 at 11:35
1

@jeroenh - I've edit that answer. You're wrong. I've been using this for years on SQL Server. – Preet Sangha Sep 22 '11 at 11:42
1

You're absolutely right, learned something new today. Truly sorry – jeroenh Sep 22 '11 at 12:04
@Preet Sangha : what you mean saying "no need to do ..."? This is not a hack but ANSI SQL construction, unlike `ISNULL` is T-SQL specific. I'm cqan't understand why "no need to do" sentense, is there any side effects you mean or what? More over, from my point of view condition with `OR` is much cleaner then `ISNULL` – sll Sep 22 '11 at 14:10
Hi, I only meant that there is a shorter construction built into SQL server. Ultimately it comes down to choice, and i prefer a less code over more. If ANSI compliance is a requirement then of course my statement is wrong, but i don'trecall that that was a question requirements. – Preet Sangha Sep 22 '11 at 19:56

score 0 · Answer 3 · answered Feb 13 '13 at 06:50

0

You can also use case statement in the where clause

where e.ZoneId = case when @zoneid=0 then e.zoneid else @zoneid end

answered Feb 13 '13 at 06:50

Shreyas Maratha

177
2
2

How can I add less than condition instead of "=" in S.Type = ISNULL(@serie_type,S.Type) – Ali Shahzad Sep 03 '13 at 07:10

score 0 · Answer 4 · answered Dec 31 '13 at 02:46

The very clean approach will be define your @serie_type to be 0. Take a look below:

CREATE PROCEDURE Report_CompanySerie
    @company    INT,
    @serie_type INT = 0,
    @from       DATE,
    @to         DATE
AS
BEGIN
    SELECT
        *
    FROM Company C
        JOIN Series S ON S.Company_FK = C.Id
    WHERE C.Id = @company 
        AND S.Created >= @from
        AND S.Created <= @to
/* HERE IS MY PROBLEM */        
        AND (@serie_type = 0 OR S.SerieType = @serie_type)
    END
GO

Select all if parameter is null in stored procedure

4 Answers4

Linked