What is the best way to perform query by ID or instead get all?-CodePudding

I have to do a query filter but I don't want to repeat code. I would like to do it in one simple query, the simplest possible.

Technology

SQL Server v18

The Acceptance Criteria

Get by ID if the ID has a value (one single record in this case) OR IF ID = NULL then GET ALL

I've been trying with AND, ORs, IS NOT NULL, etc. But I would like to get the records in a simplest query.

Currently this is my query:

DECLARE @EventLogId int = null
SET @EventLogId = NULL

-- Insert statements for procedure here
SELECT 
    EL.EventLogId,
    EL.EventLogDateTime,
    EL.Username,
    EL.EventLogTypeId,
    EL.ActionTypeId,
    EL.ProfessionalId,
    EL.BioId,
    EL.[Message],
    EL.ExceptionMessage,
    EL.StackTrace,
    ELTypes.[Name] AS EventLogTypeName,
    ATypes.[Name] AS ActionTypeName
FROM 
    dbo.EventLog EL
INNER JOIN 
    EventLogType ELTypes ON ELTypes.Id = EL.EventLogTypeId
INNER JOIN 
    ActionTypes ATypes ON ATypes.ActionTypeId = EL.ActionTypeId
WHERE 
    EL.EventLogId = @EventLogId 
    AND NOT(EL.EventLogId IS NULL)

Thanks for the help!

CodePudding user response：

A common pattern for this in SQL Server is

SELECT 
    EL.EventLogId,
    EL.EventLogDateTime,
    EL.Username,
    EL.EventLogTypeId,
    EL.ActionTypeId,
    EL.ProfessionalId,
    EL.BioId,
    EL.[Message],
    EL.ExceptionMessage,
    EL.StackTrace,
    ELTypes.[Name] AS EventLogTypeName,
    ATypes.[Name] AS ActionTypeName
FROM 
    dbo.EventLog EL
INNER JOIN 
    EventLogType ELTypes ON ELTypes.Id = EL.EventLogTypeId
INNER JOIN 
    ActionTypes ATypes ON ATypes.ActionTypeId = EL.ActionTypeId
WHERE 
    EL.EventLogId = @EventLogId OR @EventLogId IS NULL 
OPTION (RECOMPILE)

The OPTION (RECOMPILE) prevents SQL Server from using the same plan for both cases, which would be bad.

CodePudding user response：

You're so close:

WHERE 
    EL.EventLogId = @EventLogId 
    OR @EventLogId IS NULL

This problem is called "dynamic search conditions". The alternative is dynamic sql. When you have some time, read Erland Sommarskog's excellent article about this.

CodePudding user response：

Just another option ... coalesce()

WHERE  EL.EventLogId = coalesce(@EventLogId,EL.EventLogId)