Stored procedures problem - 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

SQL Server Development (2000)

Stored procedures problem

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-21 : 00:26:08

Matthew writes "Hi,

I have a few questions regarding stored procedures. Please help.

QUESTION 1.

I have a stored procedure as such (simplified):

*******************************************************
@mode varchar(10),
@member_id int

IF @mode = 'single' THEN
SELECT name FROM members WHERE id = @member_id
ELSE
SELECT name FROM members
*******************************************************

I am trying to achieve the above with a single SELECT query in this logic:

SELECT name FROM members
IF @mode = 'single'
<code to filter a single ID from the selected members record>

While it may not be the most efficient, I'm trying to learn how to do the above, so I would appreciate if someone could help me.

QUESTION 2.

How do you use filters in stored procedures? In the context of question 1.

QUESTION 3.

Which was is better and more efficient?

SELECT members.name, business.name
FROM members
INNER JOIN business ON business.id = members.business_id

or

SELECT members.name, business.name
FROM members, business
WHERE members.business_id = business.id

Thanks for the help."

Nazim
A custom title

1408 Posts

Posted - 2002-03-21 : 01:06:39

As for your Question 1 and 2

Check Case statement in BOL.

to demonstrate

select * from members
where state like (case @mvar when 'Single' then '%' else @memberid end)

About your Question 3
Check this link http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=12983

--------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources