CODEWITHSUNDEEP
sqlasp.netsql-server-2014
ThunD3eR
ThunD3eR

Reputation: 3456

How to optimize null check in SQL?

I have a procedure that gets me some data based on a date.

ALTER PROCEDURE [dbo].[GetLatestTicketsByDate]
    @Datemin datetime2,
AS
BEGIN
    DECLARE @LatestTickets TABLE
    (
        DomainId bigint, 
        SoldTickets bigint
    )

    INSERT INTO @LatestTickets(DomainId,SoldTickets)
        SELECT  
            DomainId, SoldTickets 
        FROM 
            DomainDetailDataHistory
        WHERE  
            [Date] >= @Datemin

    SELECT * FROM @LatestTickets
END

Now I want to make this procedure smarter by adding a new paramater to it BUT this paramater is optional.

My code looks like this: 

    ALTER PROCEDURE [dbo].[GetLatestTicketsByDate]
        @Datemin datetime2,
        @Id uniqueidentifier = NULL 
    AS
    BEGIN
        DECLARE @LatestTickets TABLE
        (
            DomainId bigint, 
            SoldTickets bigint
        )

        IF (@Id IS NULL)
        BEGIN 
            INSERT INTO @LatestTickets(DomainId,SoldTickets)
                SELECT DomainId, SoldTickets 
                FROM DomainDetailDataHistory
                WHERE [Date] >= @Datemin

            SELECT * FROM @LatestTickets
        END
        ELSE
        BEGIN 
            INSERT INTO @LatestTickets(DomainId,SoldTickets)
                SELECT DomainId, SoldTickets 
                FROM DomainDetailDataHistory
                WHERE Id = @Id AND  [Date] >= @Datemin

            SELECT * FROM @LatestTickets
     END
END

This works but I don't like this structure and was wondering if there is a better way?

I tried this: 

INSERT INTO @LatestTickets(DomainId,SoldTickets)
    SELECT DomainId, SoldTickets 
    FROM DomainDetailDataHistory
    WHERE (@Id IS NOT NULL AND Id = @Id) AND [Date] >= @Datemin

SELECT * FROM @LatestTickets

But it did not work.

Upvotes: 1

Views: 33

Answers (1)

juergen d
juergen d

Reputation: 204924

Use @Id IS NULL OR Id = @Id

INSERT INTO @LatestTickets(DomainId,SoldTickets)
SELECT  DomainId, SoldTickets
FROM DomainDetailDataHistory
WHERE (@Id IS NULL OR Id = @Id) AND  [Date] >= @Datemin

Upvotes: 4

Related Questions