?
Solved

IF ELSE in SQL Server stored procedure

Posted on 2010-09-13
7
Medium Priority
?
288 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
[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 Comments
 
LVL 93

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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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 200 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
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
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

770 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