CODEWITHSUNDEEP
sqlasp.netsql-serverstored-procedures
user2279712
user2279712

Reputation: 81

Stored procedure if record exists then update

I'm trying to check if a record exists and then update it if it does

Here is what I current have: (Which obviously does not work)

    CREATE PROCEDURE dbo.update_customer_m
       @customer_id             INT                      , 
       @firstname               VARCHAR(30)          , 
       @surname                 VARCHAR(30)              , 
       @gender                  VARCHAR(6)               , 
       @age                     INT                  , 
       @address_1               VARCHAR(50)      , 
       @address_2               VARCHAR(50)              ,
       @city            VARCHAR(50)              ,
       @phone           VARCHAR(10)              ,
       @mobile          VARCHAR(11)              ,
       @email           VARCHAR(30)              ,               
AS 
    IF EXISTS 
    (
       SELECT *
       FROM dbo.Customer
       WHERE CustID = @customer_id
    )
BEGIN 
       UPDATE dbo.Customer
           SET Firstname = @firstname,  Surname = @surname, Age = @age, Gender = @gender, Address1 = @address_1, Address2 = @address_2, City = @city, Phone = @phone, Mobile = @mobile, Email = @email
       WHERE CustID = @customer_id
END

Is there a better way of doing this that works?

Upvotes: 1

Views: 311

Answers (3)

odlan yer
odlan yer

Reputation: 771

SELECT *
   FROM dbo.Customer
   WHERE CustID = @customer_id
//add this
IF @@ROWCOUNT>=1
BEGIN 
   UPDATE dbo.Customer
       SET Firstname = @firstname,  Surname = @surname, Age = @age, Gender = @gender, Address1 = @address_1, Address2 = @address_2, City = @city, Phone = @phone, Mobile = @mobile, Email = @email
   WHERE CustID = @customer_id
END

Upvotes: 0

Hemen Shams
Hemen Shams

Reputation: 11

if block not needed, first select if has no row, Update block do nothing and no row is affected.

just write your update in this procedure.

but maybe you want to write else if for this procedure, if it's OK, you can you IF and ELSE. in IF block you can write this Update, and in ELSE block you can do another that want.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Why both checking first? The update will update no rows if the row doesn't exist:

   UPDATE dbo.Customer
       SET Firstname = @firstname,  Surname = @surname, Age = @age, Gender = @gender,
           Address1 = @address_1, Address2 = @address_2, City = @city,
           Phone = @phone, Mobile = @mobile, Email = @email
       WHERE CustID = @customer_id;

The if is not needed.

Upvotes: 3

Related Questions