Updating a Record with Stored Procedure

NovicePGM2011 · #1 (**permalink**) November 12th, 2011, 06:31 PM

This forum seemed like a party that died years ago!

However, I remain hopeful someone is still around to answer my question.

Chapter 10. Page 315 talks about performing updates using stored procedures.

Here is the piece of code I am referring to:

Code:

CREATE PROCEDURE spUpd_MyContacts
    @ContactID      int
  , @FirstName      nvarchar(50)
  , @LastName       nvarchar(50)
  , @Phone          nvarchar(25)
AS
   UPDATE MyContacts 
   SET FirstName = @FirstName
     , LastName = @LastName
     , Phone = @Phone

spUpd_MyContacts 5, 'Betrice', 'Rubble', '(222) 234-7654'

When I run this code, it updates all the records in the table rather then only updating record with the ID of 1. Is this code suppose to update all records? Or just the record with primary key of 5?

My second question is, how do I update a record without using a primary key? Can I specify other attributes in a where clause rather then using a primary key?

Thanks everyone!

Novice

Paul_Turley · #2 (**permalink**) November 12th, 2011, 08:48 PM

You're absolutely right. I beleive that when the stored procedure script was pasted into the manuscript, the last line was omitted. My appologies. The code should read:

CREATE PROCEDURE spUpd_MyContacts
@ContactID int
, @FirstName nvarchar(50)
, @LastName nvarchar(50)
, @Phone nvarchar(25)
AS
UPDATE MyContacts
SET FirstName = @FirstName
, LastName = @LastName
, Phone = @Phone
WHERE ContactID = @ContactID

Regarding your second question, any column or combination of columns can be used in a WHERE clause to qualify records. Just keep in mind that only the primary key is used to guarantee uniqueness.

NovicePGM2011 · #3 (**permalink**) November 12th, 2011, 09:12 PM

Quote:

Originally Posted by Paul_Turley

You're absolutely right. I beleive that when the stored procedure script was pasted into the manuscript, the last line was omitted. My appologies. The code should read:

CREATE PROCEDURE spUpd_MyContacts
@ContactID int
, @FirstName nvarchar(50)
, @LastName nvarchar(50)
, @Phone nvarchar(25)
AS
UPDATE MyContacts
SET FirstName = @FirstName
, LastName = @LastName
, Phone = @Phone
WHERE ContactID = @ContactID

Regarding your second question, any column or combination of columns can be used in a WHERE clause to qualify records. Just keep in mind that only the primary key is used to guarantee uniqueness.

Thanks Paul!! I was about to give up!
This is all new to me.

So lets say using the above code, but instead of specifying the update by Id as you just mentioned, I wanted to use the same stored procedure but performed the update by using the stored procedure with a where clause.

Example: Update record is First name = paul and last name = Turley.

I know in the real world this would be the wrong way but I for the sake of learning.

Thanks again!

Paul_Turley · #4 (**permalink**) November 12th, 2011, 09:20 PM

If you wanted to use the first and last name to identify the record and also have the ability to pass in new values for the same two columns, you could do this:

create procedure spUpd_ContactName
@FirstName nvarchar(50)
, @LastName nvarchar(50)
, @NewFirstName nvarchar(50)
, @NewLastName nvarchar(50)
as
update MyContacts
set FirstName = @NewFirstName, LastName = @NewLastName
where FirstName = @FirstName and LastName = @LastName

NovicePGM2011 · #5 (**permalink**) November 12th, 2011, 09:32 PM

Quote:

Originally Posted by Paul_Turley

If you wanted to use the first and last name to identify the record and also have the ability to pass in new values for the same two columns, you could do this:

create procedure spUpd_ContactName
@FirstName nvarchar(50)
, @LastName nvarchar(50)
, @NewFirstName nvarchar(50)
, @NewLastName nvarchar(50)
as
update MyContacts
set FirstName = @NewFirstName, LastName = @NewLastName
where FirstName = @FirstName and LastName = @LastName

Thanks again. In my Scenario, the First Name and Last names are primary Keys. This scenario is just an example.

In the real world, the name could be a company name, example: BestBuy.
Since we are not going to be changing the company name, we make the name the primary Key.

However, I want to update all other fields using one or multiple attributes.
Example: Where company name = 'BestBuy' and Company Location = 'MPLS'.

This way we can update the address phone numbers but not the name. This scenario is perfect if there is only one branch per city.

By the way, how would I called the example you mentioned above?
I don't want to change the name but instead is it as an attribute in the Where clause.

Thanks a Million!

Novice

NovicePGM2011 · #6 (**permalink**) November 12th, 2011, 09:55 PM

Here is an example of a table for a store:

In this table I would like to use the Stored Procedure to
update all aspects of the tblStore using the storeName and the StoreBranch as a specifier in a where clause.

So if I have multiple stores I can only update one.

Code:

CREATE TABLE tblStore(
StoreName       VARCHAR(30),
StoreBranch     VARCHAR(30),
CONSTRAINT pktblStore PRIMARY KEY (StoreName, StoreBranch),
StoreAddress    VARCHAR(30) NOT NULL,
StoreCity       VARCHAR(30) NOT NULL,
StoreState      CHAR(2) NOT NULL,
StoreZip        CHAR(9)  NOT NULL,
StorePhone      CHAR(10) NOT NULL,
);

Just needed to illustrate my thinking.

Novice

NovicePGM2011 · #7 (**permalink**) November 12th, 2011, 11:15 PM

Quote:

Originally Posted by Paul_Turley

If you wanted to use the first and last name to identify the record and also have the ability to pass in new values for the same two columns, you could do this:

create procedure spUpd_ContactName
@FirstName nvarchar(50)
, @LastName nvarchar(50)
, @NewFirstName nvarchar(50)
, @NewLastName nvarchar(50)
as
update MyContacts
set FirstName = @NewFirstName, LastName = @NewLastName
where FirstName = @FirstName and LastName = @LastName

Here is what I did per your directions to update the table or a record without changing the name. Meaning I used the Store Name and the Store Branch in the where clause to update the table.

Code:

CREATE PROCEDURE spUpd_StoreInfo
  @StoreName    VARCHAR(30)
, @StoreBranch  VARCHAR(30)
, @StoreAddress VARCHAR(30)
, @StoreCity    VARCHAR(30)
, @StoreState      CHAR(2)
, @StoreZip        CHAR(9)
, @StorePhone      CHAR(10)
AS
UPDATE tblStore
SET StoreAddress = @StoreAddress
  , StoreCity    = @StoreCity
  , StoreState   = @StoreState
  , StoreZip     = @StoreZip
  , StorePhone   = @StorePhone
WHERE StoreName  = @StoreName AND StoreBranch = @StoreBranch

spUpd_StoreInfo 'Maple Grove', 'Best Buy','99689 Ruggs Street', 'Minneapolis', 'MN',
'55443','6122342345';

Thanks for your help!

kes2015 · #8 (**permalink**) October 20th, 2015, 02:21 PM

What does the @ symbol in the Stored procedure do?
I have been trying to find out how to edit a data table using store procedure.
I need code that would get the Id for the row being edited. If there was a way to upload an image here it would be more beneficial.

Need to download code?

Navigation