Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

IF ELSE in SQL Server stored procedure

Posted on 2010-09-13
7
Medium Priority
?
296 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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…

610 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