If.. else?

How do I do if- else in SQL?

I want to insert something into a table, but if column1=c1 and column2=c2, it returns a message indicating that column1/column2 already exist.

How do I do that?

Thanks.
samliamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dasariCommented:
0
samliamAuthor Commented:
OK. I have a stored proc like:

if exists(...)
else  print 'item already exists.'

How do I get the 'exist' message back to my calling program?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Dishan FernandoSoftware Engineer / DBACommented:
IF(@column1= 'c1' and @column2 = 'c2')
BEGIN
   // do something
END
ELSE
BEGIN
   // do something
END


if exists(...)
begin
end
else
    RETURN -1
0
samliamAuthor Commented:
In my stored proc:

if exists( select * from @table where year='2004' and model='@model')
 RAISERROR ('Item already exists!')

This does not work, I have to use dynamic sql since @table is a variable. How do I use dynamic sql here?

Will up the points to 100.

Thanks.
0
samliamAuthor Commented:
So if I return '-1' in the stored proc
In the calling program:
ExecuteNonQuery() will return '-1'? or do I have to add an output parameter?
0
nigelrivettCommented:
create procedure sptest
@table varchar(128) ,
@madel varchar(50) ,
@result varchar(20) out
as

declare @i int, @sql nvarchar(1000)
select @sql = 'select @i = 1 where exists (select * from ' + @table + ' where year='2004' and model=''' + @model + ''')'

exec sp_execute @sql, N'@i int out', @i out
if @i = 1
select @result = 'item exists'
else
select @result = 'item does not exists
go

0
Dishan FernandoSoftware Engineer / DBACommented:
are you using c#??
0
Dishan FernandoSoftware Engineer / DBACommented:
you can access the return value via return parameter.
OR
you can assign it to output variable and then check it.

If you want I can send the code sample, how you can access the return values using C#. :)
0
ShogunWadeCommented:
Why not simply add a unique contraint on the table ?
0
samliamAuthor Commented:
nigelrivett,

I get this error message using your code:

SqlException: Procedure expects parameter '@handle' of type 'int'.

Do I need to add a parameter after sp_execute?
0
Dishan FernandoSoftware Engineer / DBACommented:
create procedure sptest
@table varchar(128) ,
@model varchar(50) ,
@result varchar(20) out
as

declare @i int, @sql nvarchar(1000)
select @sql = 'select @i = 1 where exists (select * from ' + @table + ' where year='+2004+' and model=''' + @model + ''')'

exec sp_execute @sql, N'@i int out', @i out
if @i = 1
select @result = 'item exists'
else
select @result = 'item does not exists'
go
0
Dishan FernandoSoftware Engineer / DBACommented:
use sp_executesql @sql, N'@i int out', @i out
0
ShogunWadeCommented:
Why not simply add a unique contraint on the table ?
0
samliamAuthor Commented:
I tried sp_executesql but I got syntax error.
0
Dishan FernandoSoftware Engineer / DBACommented:
Hi sam,
this is what im tested.

create procedure sptest
@table varchar(128) ,
@model varchar(50) ,
@result varchar(20) out
as

declare @i int, @sql nvarchar(1000)
--select @sql = 'select @i = 1 where exists (select * from ' + @table + ' where year='+2004+' and model=''' + @model + ''')'
select @sql = 'select @i = 1 where exists (select * from ' + @table + ')'

exec sp_executesql @sql, N'@i int out', @i out
if @i = 1
select @result = 'item exists'
else
select @result = 'item does not exists'
go


and to run sp..

declare @result varchar(20)
exec sptest 'Agent', '', @result out
print @result

it gives correct result. what is the systax error it gives?
0
samliamAuthor Commented:
I got "SqlException: Line 1: Incorrect syntax near '@i' "

Here is the code:
 .........
    @table varchar(20),
    @model varchar(30),
     ...........
    @result int out
AS

begin

declare @sql0 nvarchar(300)
declare @i int

select @sql0='@i=1 where exists (select * from '+@table+' where year="2004" and model="'+@model+'")'
exec sp_executesql @sql0, N'@i int out', @i out

if @i=1
  select @result=-1
else
  select @result=1
  begin
    ...... code that worked ...
  end
 ............

0
Dishan FernandoSoftware Engineer / DBACommented:
select @sql0='@i=1 where exists (select * from '+@table+' where year="2004" and model="'+@model+'")'

must be change as

select @sql0='SELECT @i=1 where exists (select * from '+@table+' where year="2004" and model="'+@model+'")'

u missed SELECT
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShogunWadeCommented:
Has your question been answered?   If so could you please close it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.