Ra3IDeN Ra3IDeN - 7 months ago 9
SQL Question

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.

Answer

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
Comments