Solved

If.. else?

Posted on 2003-12-08
22
1,010 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
  • 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
 
LVL 8

Expert Comment

by:dishanf
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:dishanf
ID: 9903263
are you using c#??
0
 
LVL 8

Expert Comment

by:dishanf
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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:dishanf
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:dishanf
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:dishanf
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:
dishanf earned 50 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

861 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now