Solved

IF ELSE in SQL Server stored procedure

Posted on 2010-09-13
7
240 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:centem
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

728 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

13 Experts available now in Live!

Get 1:1 Help Now