Solved

IF ELSE in SQL Server stored procedure

Posted on 2010-09-13
7
261 Views
Last Modified: 2012-05-10
Greetings,
I have an SP as follows:
CREATE PROCEDURE insertName
@fname,@lname
AS
IF EXISTS(SELECT fn,ln FROM names WHERE fn=@fname AND ln=@lname)
ELSE
INSERT INTO names(fn,ln) VALUES(@fname,@lname)

Getting syntax error near 'ELSE'. What is the correct syntax if its not correct.
Thanks to anyone who could help.
0
Comment
Question by:centem
7 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33662731
Perhaps:
CREATE PROCEDURE insertName
@fname,@lname
AS BEGIN
IF NOT EXISTS(SELECT fn,ln FROM names WHERE fn=@fname AND ln=@lname)
INSERT INTO names(fn,ln) VALUES(@fname,@lname)
END

Open in new window

0
 
LVL 60

Expert Comment

by:chapmandew
ID: 33662734
CREATE PROCEDURE insertName
@fname,@lname
AS
IF NOT EXISTS(SELECT fn,ln FROM names WHERE fn=@fname AND ln=@lname)
BEGIN
INSERT INTO names(fn,ln) VALUES(@fname,@lname)
END

OR

CREATE PROCEDURE insertName
@fname,@lname
AS
IF EXISTS(SELECT fn,ln FROM names WHERE fn=@fname AND ln=@lname)
BEGIN
-- do something here.
END
ELSE
INSERT INTO names(fn,ln) VALUES(@fname,@lname)
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33662848
generally i prefer this
CREATE PROCEDURE insertName
@id int,
@fname varchar(50),
@lname varchar(50)
AS 


IF EXISTS(SELECT fn,ln FROM names WHERE fn=@fname AND ln=@lname)  
BEGIN  
INSERT INTO names(fn,ln) VALUES(@fname,@lname)  
END
ELSE
BEGIN
   Update names
   Set fn = @fname,
       ln = @lname
   Where id = @id
END

Open in new window

0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:centem
ID: 33663098
Thank you very much for the assistance!

If I want somehow to add a message to an ASP label that the row already exists how would I do that? My internet research shows some people use @@rowcount or SELECT 'Already Exists' message after the IF EXISTS but I'm having trouble wrapping my head around how to use that in an ASP lable. Thanks.
0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 50 total points
ID: 33663343
One method is using output parameter,but that not be simple.
aS You said, you can achieve by this also.
CREATE PROCEDURE insertName  
@id int,  
@fname varchar(50),  
@lname varchar(50)  
AS   
  
  
IF EXISTS(SELECT fn,ln FROM names WHERE fn=@fname AND ln=@lname)    
BEGIN    
INSERT INTO names(fn,ln) VALUES(@fname,@lname)    
Select 'Data  Inserted.'Message
END  
ELSE  
BEGIN  
  select'Already exists.'Message
END

Open in new window

0
 

Author Comment

by:centem
ID: 33664171
Brischoft,
is the 'Data Inserted.' Message obtained through a method like the ExecuteNonQuery? I was thinking of maybe displaying message in asp label if ExecuteNonQuery = 0. Is there a better way?
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33664371
There should b btr way.bt m not knwing as m cf dev.
Bt if ur query is small then its btr if u just insert through asp.
U cn easily display msg n do validatin.
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.

776 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