We help IT Professionals succeed at work.

IF ELSE in SQL Server stored procedure

326 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.
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
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

CERTIFIED EXPERT
Awarded 2008
Awarded 2008

Commented:
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)
Bhavesh ShahLead Analyst
CERTIFIED EXPERT
Top Expert 2010

Commented:
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

Author

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.
Lead Analyst
CERTIFIED EXPERT
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

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?
Bhavesh ShahLead Analyst
CERTIFIED EXPERT
Top Expert 2010

Commented:
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.