?
Solved

If.. else?

Posted on 2003-12-08
22
Medium Priority
?
1,042 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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

777 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