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.

Antihalation

see this: https://mcmap.net/q/1170602/-optimal-search-queries/… – Boony

Answer 1

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.

Inapprehensible

@jeroenh : looks like you'are wrong, ISNULL ( check_expression , replacement_value ) - "Returns the same type as check_expression." – Ferguson

@jeroenh - I've edit that answer. You're wrong. I've been using this for years on SQL Server. – Inapprehensible

You're absolutely right, learned something new today. Truly sorry – Tyika

@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 – Ferguson

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. – Inapprehensible

Answer 2

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)

Ferguson

Its a perfact answer as check with both side. – Biased

Answer 3

You can also use case statement in the where clause

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

Marabou

How can I add less than condition instead of "=" in S.Type = ISNULL(@serie_type,S.Type) – Pozsony

Answer 4

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

Recept