Stored Procedure IF Statement - 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 Procedure IF Statement

Author

Topic

rme8494
Yak Posting Veteran

98 Posts

Posted - 2003-07-27 : 15:01:36

Hi Everyone,
I'm trying to run the query below in a SP. However I'm getting a couple error messages. I've never done and IF statment before. Any help would be appreciated!

SQL Query
SELECT COUNT(*)
FROM dbo.TroubleTicket T2
WHERE T2.ClosedMonth = dbo.TroubleTicket.ClosedMonth AND
IF @Access <> 'Both' dbo.TroubleTicket.Access = @Access AND
T2.NSDB = @NSDB

Error
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'IF'.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@Access'.

Ryan Everhart
SBC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-27 : 15:30:54

IF is a flow-control statement and not a "function" that can return values.

NOte that if "a" and "b" are both boolean (conditional) expressions, then:

IF a THEN b

is equivalent to:

(Not a) OR b

Thus, you can translate your statement into:

SELECT COUNT(*)
FROM dbo.TroubleTicket T2
WHERE T2.ClosedMonth = dbo.TroubleTicket.ClosedMonth AND
(@Access = 'Both' OR T2.NSDB = @NSDB)

Hopefully, you can look at it and see that is logically makes sense...

- Jeff

rme8494
Yak Posting Veteran

98 Posts

Posted - 2003-07-27 : 15:43:50

Jeff
Thanks for your help, but still a little confused... Here is a better example of what I want to do.

WHERE
IF @Access <> 'Both'
dbo.TroubleTicket.Access = @Access AND
T2.NSDB = @NSDB)
ELSE
T2.NSDB = @NSDB)

Bascialy IF @Access is NOT 'Both' run both conditions else run one condition.

Ryan

Ryan Everhart
SBC

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-27 : 16:25:58

apply what I was explaining, it's easy to do:

WHERE T2.ClosedMonth = dbo.TroubleTicket.ClosedMonth AND
(@Access = 'Both' OR (Access = @Access AND T2.NSDB = @NSDB))

hopefully that makes sense.

- Jeff

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-07-27 : 16:58:06

WHERE
(@Access <> 'Both' AND dbo.TroubleTicket.Access = @Access AND
T2.NSDB = @NSDB)
OR
(@Access = 'Both' AND T2.NSDB = @NSDB)

Ryan, what does the SBC mean?

- Vit

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-27 : 17:27:07

I think Vit has the answer, that looks right to me.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources