T-SQL Optional Param Nullif - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

T-SQL Optional Param Nullif

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-08 : 08:07:43

Anthony writes "Dear SQL Team,

I use SPROCs extensively for my ASP.NET 1.1 web application. Many of my stored-procedures have optional query parameters. The method I use is to set a param to a value of -1 when it is to be ignored (optional).

I was using the following method #1 for an optional params until I discovered that it was incorrectly returning a limited result set.

Method "#1"
Where SeasonID = ISNULL(NULLIF(@SeasonID,-1),SeasonID)

It was returning only those records that had a non <NULL> value for this field instead of ignoring this field completely for the selection criteria

By changing it to the following method #2 I got it to work as expected, but this throws all my existing queries using method #1 into question.
Method "#2"
Where ((NULLIF(@SeasonID,-1) IS NULL) OR (SeasonID = @SeasonID))

Can you please tell me why method #1 did not work and #2 did?

Many thanks in advance.

The complete SROC is included below:
============================================

CREATE PROCEDURE dbo.Products_ByType_Admin_

@ID int,
@OnlyStoreID int = 0,
@SeasonID int = -1

AS

SELECT
[ProductID]
, CAST([ProductName] as nvarchar(100)) + ' (' + CAST(ps.[ProductStatusName] as nvarchar(100)) + ')' as ProductName
, ListPrice,SitePrice,ImageFileSmall ,ManufacturerID,ShortDescription
FROM
bvc_Product p
join
bvc_ProductStatus ps on p.Status = ps.ProductStatusID
WHERE
ProductTypeID=@ID
AND
(
OnlyStoreID = ISNULL(NULLIF(@OnlyStoreID,0),OnlyStoreID)
OR
OnlyStoreID = 0
)
AND
SeasonID = ISNULL(NULLIF(@SeasonID,-1),SeasonID)
ORDER BY
ProductName"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-01-08 : 08:20:12

Where SeasonID = ISNULL(NULLIF(@SeasonID,-1),SeasonID)

will never return NULL values in SeasonID, since NULL is never equal to NULL. i.e., the boolean expression

NULL = NULL

will not return TRUE, so that expression in criteria will not return the rows you want. You must use:

NULL Is NULL

which will return TRUE when comparing two null values.

The big question is: Is is valid for someone to pass in a Null value for the @SeasonID parameter to return only rows where SeasonID is null?

If the answer is NO, then simplify your OR expression:

WHERE (@SeasonID = -1) OR (@SeasonID = SeasonID)

If the answer is YES, then your existing OR expression will not work without some changes since, again, @SeasonID = SeasonID will never return TRUE if they both are NULL:

WHERE (@SeasonID = -1) OR (@SeasonID = SeasonID) OR (@SeasonID Is Null AND SeasonID Is Null)

Long and messy, and most likely not efficient (OR's are notoriously tough to optimize due to the various branching possibilities), but that's the logic you need.

- Jeff

Kristen
Test

22859 Posts

Posted - 2007-01-08 : 12:40:25

We use NULL for "optional" parameters, and then do

Where (@SeasonID IS NULL OR SeasonID = @SeasonID)

whilst this will NOT allow explicit location of rows with a NULL SeasonID column, that isn't something we want in practice, and in fact making a "Null" parameter mean "Everything" or "Don't care" seems to fit in with what users want most of the time.

We allow for an Empty String to be provided instead - as that would be the norm off a Web Form, for example:

Where (NullIf(@SeasonID, '') IS NULL OR SeasonID = @SeasonID)

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 17:52:17

WHERE @SeasonID IN (-1, SeasonID)?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources