November 20, 2009 at 8:19 am
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-2 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?
November 20, 2009 at 8:28 am
Alter Proc sp_Test (@a varchar(10), @b-2 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
-- Gianluca Sartori
November 20, 2009 at 8:33 am
Gianluca Sartori (11/20/2009)
Alter Proc sp_Test (@a varchar(10), @b-2 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
November 20, 2009 at 2:21 pm
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!
November 23, 2009 at 1:10 am
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.
-- Gianluca Sartori
November 23, 2009 at 1:56 pm
I got the article. Thanks very much. Very helpful.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply