IF...ELSE - 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

General SQL Server Forums

New to SQL Server Programming

IF...ELSE

Author

Topic

JeffT
Posting Yak Master

111 Posts

Posted - 2006-02-13 : 11:14:33

Hi,

I created a stored procedure to delete rows from a table and, being new to SQL, am wondering if I can do something like this. Actually, I'm testing this out and finding that the 2nd "IF" statement (@parm = 0) is the only one NOT working. Are mutiple IF...ELSEs not allowed in SQL ? I've googled this questiona and can't seem to get a definitive answer. Thanks, Jeff

F (@parm = ' ')
DELETE from dmapgmr1.XMLRepositoryTemp
WHERE datediff(dd, DT, getdate()) = 1
ELSE
IF (@parm = 0)
DELETE from dmapgmr1.XMLRepositoryTemp
ELSE
DELETE from dmapgmr1.XMLRepositoryTemp
WHERE datediff(day, DT, getdate()) > @parm

Kristen
Test

22859 Posts

Posted - 2006-02-13 : 11:41:41

Looks fine to me - you getting some sort of error?

You are mixing strings and numbers a bit:

IF (@parm = ' ')
IF (@parm = 0)

Personally I would put some BEING/END stuff in there - in case you add some more statemetns into each block in the future:


IF (@parm = ' ')
BEGIN
    DELETE from dmapgmr1.XMLRepositoryTemp
    WHERE datediff(dd, DT, getdate()) = 1
END
ELSE
IF (@parm = 0)
BEGIN
    DELETE from dmapgmr1.XMLRepositoryTemp
END
ELSE
BEGIN
    DELETE from dmapgmr1.XMLRepositoryTemp
    WHERE datediff(day, DT, getdate()) > @parm
END

Kristen

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-13 : 11:42:55

[code]
IF (@parm = ' ')
BEGIN
DELETE from dmapgmr1.XMLRepositoryTemp
WHERE datediff(dd, DT, getdate()) = 1
END
ELSE
IF (@parm = 0)
BEGIN
DELETE from dmapgmr1.XMLRepositoryTemp
END
ELSE
BEGIN
DELETE from dmapgmr1.XMLRepositoryTemp
WHERE datediff(day, DT, getdate()) > @parm
END
[/code]

Try that..

JeffT
Posting Yak Master

111 Posts

Posted - 2006-02-13 : 12:03:03

Hi Kristen,

I am getting "(0 row(s) affected)" when I pass zero as the parm to the SP. The reason for the mixing of strings was because when I passed "null" to the SP and then checked for "null" in the SP, it did not recognize it for some reason. Only when I did a "set @parm = isnull(@parm, ' ')" to set the @parm to blank, did it work for that "IF (@parm = ' ')" statement.

Rick and Kristen, I will try the code you posted in and see what happens.

Thanks very much,
Jeff

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-13 : 12:12:42

Were you using = NULL by any chance?

If you were then you should be using IS NULL..

JeffT
Posting Yak Master

111 Posts

Posted - 2006-02-13 : 12:27:01

Rick,

Thanks very much ! I was using "= NULL" instead of "is NULL". I changed that and it worked fine. Everything is working fine now.

Thanks again !
Jeff

Kristen
Test

22859 Posts

Posted - 2006-02-13 : 13:47:15

Note that if @parm is declared as varchar you should do:

IF (@parm = '0')

whereas if @parm is declared as int/numeric/etc. then

IF (@parm = 0)

is fine but:

IF (@parm = ' ')

is then going to be a problem!

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-14 : 00:30:10

Kristen, you failed to close Red tags

Madhivanan

Failing to plan is Planning to fail

JeffT
Posting Yak Master

111 Posts

Posted - 2006-02-14 : 08:38:38

Thanks again Kristen, I'll keep that in mind !
Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources