Evaluating NULL in SP WHERE clause 1

[SHelton]

Can anybody advise how I can evaluate a null parameter in a stored procedure WHERE clause? I am trying to achieve the following:

CREATE PROCEDURE Report_Test

@MainID INT,
@SubID INT = NULL

AS

SELECT * 

FROM tbl

WHERE MainID = @MainID AND SubID = @SubID

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.

 

[jaywalk]

If you want the search to return any values of subid if you pass in NULL, then:

SELECT * FROM tbl
WHERE MainID = @MainID
AND SubID = ISNULL(@SubID, SubID)

If you want the search to return only those matching NULL, then you could try:

SELECT * FROM tbl
WHERE MainID = @MainID
AND ISNULL(SubID, -1 = ISNULL(@SubID, -1)

Hope this is what you were after,

Jaywalk

 

[Patrick2002]

the problem is that ANSI_NULLS were ON when you created the stored procedure. If you take a script of the procedure, you will see that it starts with something like

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_nameoftheprocedure]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_nameoftheprocedure]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

create proc etc ...


The trick is to drop your procedure, set ansi_nulls off, and then create your same procedure. You'll notice that now, it does work as you expected.
Greetings from Patrick

 

[SHelton]

Thanks guys - both were great suggestions. I wnet with Patrick's suggestion to keep the query simple.