Update Inside If Condition - 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 2005 Forums

Transact-SQL (2005)

Update Inside If Condition

Author

Topic

hollyquinn
Starting Member

31 Posts

Posted - 2009-02-19 : 13:02:33

Hi, I am really new to T-SQL (and for that matter SQL) and I'm nto sure I'm really getting it yet. I am hoping that someone can help me out.

I'm trying to write an update statement inside of a conditional statement. I have 3 columns called CAParticipant, FOParticipant, and SOParticipant from the a table called Table Session. I need to check to see if the three columns are not null and if they are not null make another column IsPartial = False. Otherwise IsPartial should = True. This is what I've got so far:

CREATE PROCEDURE Update_IsPartial

as
begin transaction
Begin
select CAParticipant, FOParticipant, SOParticipant from tblSession
End
IF CAParticipant <> null and FOParticipant <> null and SOParticipant <>null
Begin
UPDATE tblSession SET IsPartial = 'False'
End
ELSE
Begin
UPDATE tblSession SET IsPartial = 'True'
End
If @@ERROR <> 0
Rollback
Else
commit

I'm getting the error message that CAParticipant, FOParticipant, and SoParticipant are not valid column names. Any ideas? Is there a better way I should be doing this? Thanks,

Holly

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-19 : 13:08:24

You should only need a single update statement:

update tblSession
set isPartial = case when CAParticipant is not null and FOParticipant is not null and SOParticipant is not null then 'False'
else 'True'
end

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-19 : 13:23:54

[code]Update tblSession
Set IsPartial = Case When Coalesce(CAParticipant,FOParticipant,SOParticipant) is not null then 'False'
else 'True'
End[/code]

hollyquinn
Starting Member

31 Posts

Posted - 2009-02-19 : 13:25:36

quote:
Originally posted by SQLforGirls

You should only need a single update statement:

update tblSession
set isPartial = case when CAParticipant is not null and FOParticipant is not null and SOParticipant is not null then 'False'
else 'True'
end

Hey thanks! I see that I was trying to make it way too complicated. I love your username!

SQLforGirls
Starting Member

48 Posts

Posted - 2009-02-19 : 13:34:57

See sodeep's slightly more elegant version.

You've obviously come from a different programming background eh? Try to clear your mind, SQL is so much more efficient at what it does.

Have fun.

hollyquinn
Starting Member

31 Posts

Posted - 2009-02-19 : 13:53:50

quote:
Originally posted by SQLforGirls

See sodeep's slightly more elegant version.

You've obviously come from a different programming background eh? Try to clear your mind, SQL is so much more efficient at what it does.

Have fun.

Yes, I am a ASP.NET/VB.NET developer. My SQL knowledge is just very basic. I learn more and more everday. Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources