IF- End - 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

SQL Server 2000 Forums

SQL Server Development (2000)

IF- End - Else

Author

Topic

whamo
Starting Member

10 Posts

Posted - 2002-11-22 : 13:14:00

Can someone give me a hand with this?
It has the basic elemnts of what I'm trying to do.

If my return gets passed my if, it just runs through the end on to the else.

I can't use a "where" because if it fails, I get no return.
I always need a return of one or the other.

declare @field as etc
several times

Select top 1
@field1 = field1,
@field2 = field2,
@field3 = field3,
@field4 = field4,
@field5 = field5,
from Table name
order by Table PK desc

if @Field1 = 1
and @field2 is not null
and @field3 is not null
and @field4 is not null
and @field5 is not null

begin
select 'New'
Update Table
set Field2 = 4
where Field2 = @Field2

end else

select 'Old'
Update Table
set Field2 = 2
where Field2 = @Field2

no matter the condition of my record it passes as "2"

Any help would be great

Thanks

Edited by - Whamo on 11/22/2002 13:34:40

Edited by - Whamo on 11/22/2002 13:50:16

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-22 : 13:20:02

Updating a Table From another
http://www.sqlteam.com/item.asp?ItemID=3876

Using Case to take care of new and old at the same time.
http://www.databasejournal.com/features/mssql/article.php/1460001

whamo
Starting Member

10 Posts

Posted - 2002-11-22 : 13:36:46

quote:

Updating a Table From another
http://www.sqlteam.com/item.asp?ItemID=3876

Using Case to take care of new and old at the same time.
http://www.databasejournal.com/features/mssql/article.php/1460001

Thanks for the input, but I'm not concerned with the update. That is merely a by product of which condition passes.

My focus is that the "if" is running through my "end else" and passing both conditions.

Any ideas on how to stop this?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-22 : 13:47:16

You are setting those variables over and over again for every record that your SELECT statement returns, but only evaluating the IF condition once.

I assume the SELECT returns multiple records since you are ORDER'ing the results.

Do you want to evaluate your IF clause just once, or once per record?

- Jeff

whamo
Starting Member

10 Posts

Posted - 2002-11-22 : 13:52:42

quote:

You are setting those variables over and over again for every record that your SELECT statement returns, but only evaluating the IF condition once.

I assume the SELECT returns multiple records since you are ORDER'ing the results.

Do you want to evaluate your IF clause just once, or once per record?

- Jeff

Thanks for the feedback, I forgot a little peice of code there on top.

I am only selecting the top record (recently inserted) the Order is desc to ensure the most recent.

Therefore I would only like to evaluate the IF clause once per procedure run through.

Does that help clear it up?

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-11-22 : 14:17:37

after your ELSE, do you need a BEGIN and an END surrounding your statements???

- Jeff

whamo
Starting Member

10 Posts

Posted - 2002-11-22 : 14:28:02

quote:

after your ELSE, do you need a BEGIN and an END surrounding your statements???

- Jeff

That was it thanks!!

if @Field1 = 1
and @field2 is not null
and @field3 is not null
and @field4 is not null
and @field5 is not null

begin
select 'New'
Update Table
set Field2 = 4
where Field2 = @Field2

end
else
begin

select 'Old'
Update Table
set Field2 = 2
where Field2 = @Field2
end

I thought it was syntax issue and I apprciate your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources