Solved

If.. else?

Posted on 2003-12-08
22
993 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
Comment Utility
0
 

Author Comment

by:samliam
Comment Utility
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
Comment Utility
0
 
LVL 8

Expert Comment

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

Expert Comment

by:dishanf
Comment Utility
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
Why not simply add a unique contraint on the table ?
0
 

Author Comment

by:samliam
Comment Utility
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
Comment Utility
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
Comment Utility
use sp_executesql @sql, N'@i int out', @i out
0
 
LVL 18

Expert Comment

by:ShogunWade
Comment Utility
Why not simply add a unique contraint on the table ?
0
 

Author Comment

by:samliam
Comment Utility
I tried sp_executesql but I got syntax error.
0
 
LVL 8

Expert Comment

by:dishanf
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Has your question been answered?   If so could you please close it.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 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

10 Experts available now in Live!

Get 1:1 Help Now