Stored Procedure - parameters question

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

General SQL Server Forums

New to SQL Server Programming

Stored Procedure - parameters question

Author

Topic

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-11-23 : 13:38:16

Hi,

i have a simple stored procedure, that takes a "name" as a parameter and does a select where on it.

how can i set it so, that if i don't give it a parameter (or pass a NULL), it returns everything instead of nothing?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-23 : 13:39:52

Show us the code.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-11-23 : 13:45:38

ALTER PROCEDURE [dbo].[sp_GetTables]
-- Add the parameters for the stored procedure here
@siteID VARCHAR(10)
as begin
select * from mytable where id= @siteID
end

i want to be able to pass in nothing for siteID and get all records... right now i get nothing if i pass in a null...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-23 : 13:50:27

select * from mytable where id = COALESCE(@siteID, id)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

xrum
Yak Posting Veteran

87 Posts

Posted - 2010-11-23 : 13:55:36

that worked, thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-23 : 13:56:17

You're welcome, glad to help.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-23 : 15:24:20

but please don't use * in your code

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2010-11-24 : 00:47:50

you can also do it like this:just add a condition on site_id like this:
if @site_id=''
select @site_id=select * from your table.

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-24 : 01:32:17

Kristen
Test

22859 Posts

Posted - 2010-11-24 : 05:01:24

note that

select * from mytable where id = COALESCE(@siteID, id)

only works if all values of [id] are NOT Null. I reckon this is the case for a column called "id" but it might not be for, say, "Second Phone Number"

For that scenario you might want to do:


select * from mytable 
where (@siteID IS NULL OR id = @siteID)

But don't use SELECT * as Brett said - list the columns out, in full. (Ask if you need to know why)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-26 : 03:00:38

quote:
Originally posted by Kristen

note that

select * from mytable where id = COALESCE(@siteID, id)

only works if all values of [id] are NOT Null. I reckon this is the case for a column called "id" but it might not be for, say, "Second Phone Number"

For that scenario you might want to do:

select * from mytable
where (@siteID IS NULL OR id = @siteID)

But don't use SELECT * as Brett said - list the columns out, in full. (Ask if you need to know why)

Thats a good point thats why I always use/suggest it

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-11-27 : 06:17:15

that pattern

WHERE (@foo IS NULL or id = @foo)

can give pretty bad performance sometimes. Gail wrote a blog a while back on catch all queries. I'd advise to use dynamic sql here and just not include that check if the paramater is null. You are using dynamic sql anyway......

Here's the blog article
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2010-11-27 : 06:32:31

"I'd advise to use dynamic sql here and just not include that check if the paramater is null."

100% agree. If using SProcs though its a bit of a PITA compared to just having multiple conditional criteria tests - having to take care of permissions and all that jazz ...

One route we do is to prioritise which parameters are the most effective / frequently used and do something like:


IF @Param1 IS NOT NULL
BEGIN
   INSERT INTO @KeyList
   SELECT MyID
   FROM MyTable
   WHERE 
             MyID = @Param1
         AND (@Param2 IS NULL OR Col2 = @Param2)
         AND (@Param3 IS NULL OR Col3 = @Param3)
END
ELSE
IF @Param2 IS NOT NULL
BEGIN
   INSERT INTO @KeyList
   SELECT MyID
   FROM MyTable
   WHERE 
--           MyID = @Param1 -- No test required as would have been in earlier IF block
             Col2 = @Param2
         AND (@Param3 IS NULL OR Col3 = @Param3)
END
ELSE
    ... etc ...

   SELECT MyID, Col2, Col3, ...
   FROM @KeyList AS KL
        JOIN MyTable AS T
             ON T.MyID = KL.MyID
        JOIN OtherTable
            ON A = B

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources