Passing a null parameter

SQLServerCentral is supported by Red Gate Software Ltd.

Home Tags Articles Editorials Forums Scripts Videos Blogs QotD Books Ask SSC SQL Jobs Training Authors About us Contact us Newsletters Advertise Write for us

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Passing a null parameter

Passing a null parameter

Rate Topic

Display Mode

Topic Options

Author

Message

marty.seed

Posted Friday, November 20, 2009 8:19 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, February 03, 2010 7:44 AM
Points: 63, Visits: 125

I've probably done this a thousand times but I'm having a major brain freeze. I have a SP with three parameters being passed. The first two are madatory but the third is optional. When I run the SP with a null or '' as the option for the third I don't get any data when I should. Here's an example...

Alter Proc sp_Test (@a varchar(10), @b varchar(10), @c varchar(10))
as

Select a, b, c
From TestTable
Where a=@a
and b=@b
and c=@c

Exec sp_Test 'Test@a', 'Test@b', null
Exec sp_Test 'Test@a', 'Test@b', ''

returns no data

What am I missing with the @c parameter?

Post #822436

Gianluca Sartori

Posted Friday, November 20, 2009 8:28 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:50 AM
Points: 1,402, Visits: 2,070

Alter Proc sp_Test (@a varchar(10), @b varchar(10), @c varchar(10) = NULL)
as

Select a, b, c
From TestTable
Where a=@a
and b=@b
and c=ISNULL(@c,c)

This works, but it's not efficient.
Take a look at this article:
http://www.sommarskog.se/dyn-search-2005.html

Regards
Gianluca

How to post for quicker answers

Post #822448

zhuce-haoma

Posted Friday, November 20, 2009 8:33 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, March 17, 2010 9:20 AM
Points: 4, Visits: 169

Gianluca Sartori (11/20/2009)

Alter Proc sp_Test (@a varchar(10), @b varchar(10), @c varchar(10) = NULL)
as

Select a, b, c
From TestTable
Where a=@a
and b=@b
and c=ISNULL(@c,c)

This works, but it's not efficient.
Take a look at this article:
http://www.sommarskog.se/dyn-search-2005.html

Regards
Gianluca

it's beautiful

Post #822456

marty.seed

Posted Friday, November 20, 2009 2:21 PM

Valued Member

Group: General Forum Members
Last Login: Wednesday, February 03, 2010 7:44 AM
Points: 63, Visits: 125

I tried it using the IsNull but it took forever to run. I rewrote the SP in dynamic SQL and it took 3 seconds! Thanks for the link, it was a day saver!

Post #822708

SSSolice

Posted Friday, November 20, 2009 2:33 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 16, 2010 12:44 PM
Points: 233, Visits: 291

Try this.


Alter Proc sp_Test (@a varchar(10), @b varchar(10), @c varchar(10) = NULL)
as

Select a, b, c
From TestTable
Where a=@a
and b=@b
and (c=@C OR @c IS NULL)

Hope This Helps.

Post #822716

Gianluca Sartori

Posted Monday, November 23, 2009 1:10 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 11:50 AM
Points: 1,402, Visits: 2,070

SSSolice, your suggestion is good, it works, but I suggest that you take a look at the article I linked. You will see that "catch-all queries" or dynamic search conditions must be handled very carefully.

How to post for quicker answers

Post #823120

SSSolice

Posted Monday, November 23, 2009 1:56 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, March 16, 2010 12:44 PM
Points: 233, Visits: 291

I got the article. Thanks very much. Very helpful.

Post #823507

« Prev Topic | Next Topic »

Permissions