conditional stored procedure 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

SQL Server 2000 Forums

Transact-SQL (2000)

conditional stored procedure question

Author

Topic

davidliv
Starting Member

45 Posts

Posted - 2003-11-13 : 16:15:46

I need to create a stored proc which has a conditional WHERE clause depending on the value of a passed parameter. I'm having trouble handling the condition. I'm missing something here.

CREATE PROCEDURE Milestone_Get
(@myID int, @iShowAll int)

AS

SELECT uid, name, date, registration_confirmed
FROM tbl_members

WHERE
If @iShowAll = 0
begin
(uid = @myID) AND (registration_complete = 0)
end
else
begin
(uid = @myID)
end

GO

Thanks,
david

davidliv
Starting Member

45 Posts

Posted - 2003-11-13 : 17:28:53

If you have a lenghty select statement isn't there a more efficient way of handling the clause? Can't you handle the condition within the query statement itself?

David

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2003-11-13 : 17:31:38

There are a few methods available for handling a dynamic where clause

check out http://www.SQLTeam.com/item.asp?ItemID=2077

davidliv
Starting Member

45 Posts

Posted - 2003-11-13 : 17:46:51

I read this article, however it doesn't show how to include conditions within the WHERE section of the query.

David

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-13 : 17:50:16

You actually just need to express conditional logic using AND's and OR's.

To express:

"if A then B"

using only AND's and OR's, you would write it as:

"Not A or B"

since they both have the same truth table.

Thus, to express:

WHERE
If @iShowAll = 0
begin
(uid = @myID) AND (registration_complete = 0)
end
else
begin
(uid = @myID)
end

you would write your WHERE clause as:

WHERE uid=@myid AND ((@iShowAll <>0) OR (registration_complete = 0))

Logically, it is exactly what you want.

- Jeff

Granick
Starting Member

46 Posts

Posted - 2003-11-13 : 19:40:54

For what you are trying to do above, what Jeff has will get you what you want. If you need to have more options than a Boolean flag as in your examle, you can also use a CASE statement in your WHERE clause like this:

CREATE PROCEDURE Milestone_Get
(@myID int, @iShowAll int)

AS

SELECT
uid,
name,
date,
registration_confirmed
FROM
tbl_members
WHERE
(uid = @myID) AND
registration_complete = CASE
WHEN @iShowAll = 0 THEN registration_complete
ELSE 0
END

The nice part about the case method is that you can have more than 2 options of how your WHERE clause is done if needed.

I only mention this in case you have other needs of doing this kind of thing.

Shannon

davidliv
Starting Member

45 Posts

Posted - 2003-11-14 : 09:15:31

The CASE statements are exactly what I was looking for.

Thanks.

David

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-14 : 09:45:01

I prefer to NOT use CASE statements in WHERE clauses. Why? They are not as efficeint, or as clear in what you wish to do. and if you have multiple conditions it becomes quite a mess.

if your logic needs to be:

"IF @All=1, then return all rows, otherwise only return rows in which Cust=@Cust and Employee=@Emp"

you have two options. Without using a CASE:

WHERE @All=1 OR (Cust=@Cust and employee=@Emp)

With a CASE:

WHERE Cust = CASE WHEN @All=1 then Cust ELSE @Cust END AND
Employee = CASE WHEN @All=1 THEN Employee ELSE @emp END

The first is more efficient and more readable, and much more flexible and is quite portable.

I use CASE all the time in my SELECT's, but learning to NOT use them in WHERE clauses will make you much better at logic and much better a SQL in general.

Also more efficient: in the first situaion (w/o the CASE), as soon as SQL sees that @All=1, it can stop evaluating the rest of the expression and return the row. (A really smart optimized would realize that expression is a constant for all rows and just ignore the WHERE clause altogether). In the second example (w/ CASE), both complete CASE expressions must be evaluated for every row in the table.

- Jeff

davidliv
Starting Member

45 Posts

Posted - 2003-11-14 : 12:32:47

Thanks Jeff. That does make more sense and is more in line with what I was wanting to do. However, I can't seem to get the If statement to work within the WHERE clause.

I'm getting errors around my If statement

WHERE
If @iShowAll = 1
begin
....

---------
david

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-11-14 : 12:48:54

David, you can't use IF in your where clause. You need to use his way:

quote:

Without using a CASE:

WHERE @All=1 OR (Cust=@Cust and employee=@Emp)

With a CASE:

WHERE Cust = CASE WHEN @All=1 then Cust ELSE @Cust END AND
Employee = CASE WHEN @All=1 THEN Employee ELSE @emp END

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-14 : 13:25:13

David --

Did you read my first post carefully? I give you the exact expression to use in your WHERE clause.

WHERE uid=@myid AND ((@iShowAll <>0) OR (registration_complete = 0))

Re-read it again and see if it makes sense.

- Jeff

davidliv
Starting Member

45 Posts

Posted - 2003-11-14 : 13:37:36

Ok. I get it now. I couldn't get past not seeing the if statement in the Where clause.

In short:

WHERE
(first condition) OR (second condition in which I include my parameter condition)

Thanks for beating it into my hard head.

----------
david

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-11-14 : 14:15:19

No, that's not quite right. Again, if "A" and "B" are conditions, to express:

IF A THEN B

in a WHERE clause, you convert it to:

"WHERE (Not A) OR B"

It is important to NEGATE the condition A if you wish to say "If A is true, then B MUST be true." Which is implying, of course, that if A is false, then it doesn't matter what B is.

So, if you want:

"IF ShowAll=0 THEN EmpID = @EmpID"

it is the same as the IF A then B. Condition A is: ShowAll=0. Condition B is: EmpID=@EmpID. To convert it to using an OR, you need to NEGATE condition A:

(ShowAll=1) or (EmpID = @EmpID)

do you see logically how that works? If you leave it as:

(ShowAll=0) or (EmpID = @EmpID)

Then it will NOT return the results you want.

(I admit this is confusing ... i need to stop before i give myself a headache ... but just keep in mind that it is well worth really thinking about and learning)

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources