SQL server SP : @Param 's with sometime NULL values

Question

I am very new to SQL Server Stored Procedures, I am trying to create a SP that will give return a list of records in a table by filter via StartDate and EndDate , but there will be 'View All' Option so sometime those @Param might not contain any values.

Currently my SP is Like

CREATE PROCEDURE [dbo].[spGetBonusRun]
 (
@StartDate as DATETIME,
@EndDate as DATETIME
 )
AS

SELECT [Id]
  ,[StartDateTime]
  ,[EndDate]
  ,[Status]
FROM [Valt].[dbo].[BonusRun]
WHERE StartDateTime <= @StartDate AND EndDate >= @EndDate

How to active that ?

Answer 1

You can try something like this

CREATE PROCEDURE [dbo].[spGetBonusRun]
 (
@StartDate as DATETIME,
@EndDate as DATETIME
 )
AS

SELECT [Id]
  ,[StartDateTime]
  ,[EndDate]
  ,[Status]
FROM [Valt].[dbo].[BonusRun]
WHERE   StartDateTime <= ISNULL(@StartDate, StartDateTime)
AND  EndDate >= ISNULL(@EndDate, EndDate)

Note the use of ISNULL

Answer 2

Try this:

WHERE (StartDateTime <= @StartDate OR @StartDate IS NULL) AND (EndDate >= @EndDate OR @EndDate IS NULL)

Hope it helps.

/Klaus