Nullable parameter SP - 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 2005 Forums

Transact-SQL (2005)

Nullable parameter SP

Author

Topic

GoDaddy
Yak Posting Veteran

64 Posts

Posted - 2009-04-17 : 16:20:09

I have this SP
SP ( @Param int = null ) AS Select .... WHERE brandId = @Param

I notice that when the @Param is null ... the WHERE brandId = null doesn't return the rows that have brandId = null ...

But I tested it with WHERE brandId IS NULL, just to test, and it returns row that has brandId = null .....

How am I suppose to handle the nullable parameter ?

Something like this : WHERE brandId = @Param OR @Param IS NULL ???
Is this a correct way to handle it ?

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-04-17 : 16:25:06

There could be a better way, but you could try something like this..

declare @t table (brandid int)
insert @t
select 120 union all
select null

declare @param int
set @param = 0

select * from @t where isnull(brandid,0) = @param

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-04-17 : 16:44:39

Vijay,
I think you misunderstand the problem.

Godaddy, your code won't return the null values because null is undefined, that's why you have to use "is null" or another method.

Try this.

declare @t table (brandid int)
insert @t
select 120 union all
select null union all
select null

declare @param int
set @param = null

select * from @t where coalesce(brandid,-1) = coalesce(@param,-1)

An infinite universe is the ultimate cartesian product.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-17 : 17:10:48

You can also do something like:

SELECT 
	*
FROM 
	MyTable
WHERE
	BrandID = @Param
	OR
	(
		BrandID IS NULL
		AND @Param IS NULL
	)

Or even:

IF @ParamID IS NULL THEN
BEGIN
	SELECT *
	FROM MyTable
	WHERE BrandID IS NULL
END
ELSE
BEGIN
	SELECT *
	FROM MyTable
	WHERE BrandID = @Param
END

GoDaddy
Yak Posting Veteran

64 Posts

Posted - 2009-04-17 : 17:14:42

Thanks for the replies I did the IF, ELSE solution as a Temporary solution.

I wanted more a one statement.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources