IF ELSE in SQL Server stored procedure

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.
centemAsked:
Who is Participating?
 
Bhavesh ShahConnect With a Mentor Lead AnalysistCommented:
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
 
Patrick MatthewsCommented:
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
 
chapmandewCommented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Bhavesh ShahLead AnalysistCommented:
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
 
centemAuthor Commented:
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
 
centemAuthor Commented:
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
 
Bhavesh ShahLead AnalysistCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.