Complex 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 2005 Forums

Transact-SQL (2005)

Complex If Statement

Author

Topic

andof3
Starting Member

4 Posts

Posted - 2012-02-26 : 13:59:41

Hi, I'm trying create a complex if statement that has to satisfy the first 3 if conditions before inserting into table1, otherwise insert into table unmatched with the error message.

Here's the logic in (relative) plain language.

if @clientid is null or @clientid = '' then set @error = 'Bad Clientid'
if @productid is null or @productid = '' then set @error = 'Bad Productid'
if @title is null or @tile = '' then set @error = 'Bad Title'
then
insert into table1 (clientid, productid, title)
select @clientid, @productid, @title
else
insert into unmatched (clientid, proudutctid, title, error)
select @clientid, @productid, @title @error
end

I hope this makes sense. Any ideas how create this in SQL? Thanks.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-26 : 14:06:00

Looks almost correct. Take out the THEN (SQL's IF statements are just IF .. ELSE), set @error to null before you start and use IF @error IS NULL to determine whether to insert or not and that should be almost exactly the SQL code assuming you have @clientid, @productid and @title as variables or parameters

If it's not correct, please give a little more information about the scenario.

--
Gail Shaw
SQL Server MVP

andof3
Starting Member

4 Posts

Posted - 2012-02-26 : 14:32:23

Thanks Gail. I think where I'm getting tripped up is with the if/or statements. How do include both isnull and = '' before setting @error?

Normally the if statements I create are something like:
if (select count(1) from table1 where clientid = @clientid) > 0 begin ...

I don't see a way to say: if @client is null or @client = '' set @error = 'Bad Clientid'

Also these are variables, not parameters. Thanks.

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-26 : 15:06:32

Exactly as you have it.

if (@client is null or @client = '')
set @error = 'Bad Clientid'

Brackets are optional

--
Gail Shaw
SQL Server MVP

andof3
Starting Member

4 Posts

Posted - 2012-02-26 : 18:25:57

Ok, I'm probably over thinking this. Would the final code look something like this?

if (@clientid is null or @clientid = '')
begin
set @error = 'Missing Clientid'
if (@productid is null or @productid = '')
begin
set @error = 'Missing Productid'
if (@title is null or @title = '')
begin
set @error = 'Missing Title'
insert into unmatched (clientid, proudutctid, title, error)
select @clientid, @productid, @title, @error
end
end
end
else
begin
insert into table1 (clientid, productid, title)
select @clientid, @productid, @title
end

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-02-26 : 19:13:58

No need for the nesting

SET @Error = ''
if (@clientid is null or @clientid = '')
  set @error = 'Missing Clientid'

if (@productid is null or @productid = '')
  set @error = 'Missing Productid'

if (@title is null or @title = '')
  set @error = 'Missing Title'

IF @Error = '' -- didn't get set in any of the previous IF statements
  insert into table1 (clientid, productid, title)
  select @clientid, @productid, @title
ELSE
  insert into unmatched (clientid, proudutctid, title, error)
  select @clientid, @productid, @title, @error

--
Gail Shaw
SQL Server MVP

andof3
Starting Member

4 Posts

Posted - 2012-02-27 : 01:17:51

That is awesome! Thanks for you help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources