?
Solved

If.. else?

Posted on 2003-12-08
22
Medium Priority
?
1,050 Views
Last Modified: 2012-08-13
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.
0
Comment
Question by:samliam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 3
  • +3
22 Comments
 
LVL 4

Expert Comment

by:dasari
ID: 9902465
0
 

Author Comment

by:samliam
ID: 9902475
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
 
LVL 39

Expert Comment

by:appari
ID: 9902484
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 9902500
IF(@column1= 'c1' and @column2 = 'c2')
BEGIN
   // do something
END
ELSE
BEGIN
   // do something
END


if exists(...)
begin
end
else
    RETURN -1
0
 

Author Comment

by:samliam
ID: 9902595
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
 

Author Comment

by:samliam
ID: 9902605
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
 
LVL 18

Expert Comment

by:nigelrivett
ID: 9902654
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
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 9903263
are you using c#??
0
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 9903273
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9903464
Why not simply add a unique contraint on the table ?
0
 

Author Comment

by:samliam
ID: 9903540
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
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 9909493
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
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 9909539
use sp_executesql @sql, N'@i int out', @i out
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 9911074
Why not simply add a unique contraint on the table ?
0
 

Author Comment

by:samliam
ID: 9913892
I tried sp_executesql but I got syntax error.
0
 
LVL 8

Expert Comment

by:Dishan Fernando
ID: 9917936
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
 

Author Comment

by:samliam
ID: 9918859
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
 
LVL 8

Accepted Solution

by:
Dishan Fernando earned 200 total points
ID: 9918928
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 10021193
Has your question been answered?   If so could you please close it.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question