Can anybody advise how I can evaluate a null parameter in a stored procedure WHERE clause? I am trying to achieve the following:
MainID is always a value, but SubID may be either a value or not passed at all, in which case the default is NULL.
I have tried SET ANSI_NULLS OFF but this does not have any effect. I am trying to avoid an IF @SubID IS NULL statement and two queries.
Any help appreciated.
Code:
CREATE PROCEDURE Report_Test
@MainID INT,
@SubID INT = NULL
AS
SELECT *
FROM tbl
WHERE MainID = @MainID AND SubID = @SubID
I have tried SET ANSI_NULLS OFF but this does not have any effect. I am trying to avoid an IF @SubID IS NULL statement and two queries.
Any help appreciated.