Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning T-SQL with Microsoft SQL Server 2005 and 2008 ISBN: 978-0-470-25703-6
This is the forum to discuss the Wrox book Beginning T-SQL with Microsoft SQL Server 2005 and 2008 by Paul Turley, Dan Wood; ISBN: 9780470257036
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning T-SQL with Microsoft SQL Server 2005 and 2008 ISBN: 978-0-470-25703-6 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old November 12th, 2011, 05:31 PM
Authorized User
Points: 53, Level: 1
Points: 53, Level: 1 Points: 53, Level: 1 Points: 53, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2011
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default Updating a Record with Stored Procedure

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

Last edited by NovicePGM2011; November 12th, 2011 at 05:54 PM.
Reply With Quote
  #2 (permalink)  
Old November 12th, 2011, 07:48 PM
Wrox Author
Points: 180, Level: 3
Points: 180, Level: 3 Points: 180, Level: 3 Points: 180, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2004
Location: Vancouver, Washington, USA.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
__________________
Paul Turley
Mentor | SQL Server MVP, SolidQ
Blog: sqlserverbiblog.com
Author: SQL Server Reporting Services Recipes for Designing Expert Reports; Professional SQL Server Reporting Services (2000, 2005, 2008 & 2012), Beginning T-SQL (2000, 2005 & 2008), SSIS, SSAS & Access
Reply With Quote
  #3 (permalink)  
Old November 12th, 2011, 08:12 PM
Authorized User
Points: 53, Level: 1
Points: 53, Level: 1 Points: 53, Level: 1 Points: 53, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2011
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by Paul_Turley View Post
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!
Reply With Quote
  #4 (permalink)  
Old November 12th, 2011, 08:20 PM
Wrox Author
Points: 180, Level: 3
Points: 180, Level: 3 Points: 180, Level: 3 Points: 180, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2004
Location: Vancouver, Washington, USA.
Posts: 60
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
__________________
Paul Turley
Mentor | SQL Server MVP, SolidQ
Blog: sqlserverbiblog.com
Author: SQL Server Reporting Services Recipes for Designing Expert Reports; Professional SQL Server Reporting Services (2000, 2005, 2008 & 2012), Beginning T-SQL (2000, 2005 & 2008), SSIS, SSAS & Access
Reply With Quote
  #5 (permalink)  
Old November 12th, 2011, 08:32 PM
Authorized User
Points: 53, Level: 1
Points: 53, Level: 1 Points: 53, Level: 1 Points: 53, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2011
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by Paul_Turley View Post
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

Last edited by NovicePGM2011; November 12th, 2011 at 08:35 PM.
Reply With Quote
  #6 (permalink)  
Old November 12th, 2011, 08:55 PM
Authorized User
Points: 53, Level: 1
Points: 53, Level: 1 Points: 53, Level: 1 Points: 53, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2011
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #7 (permalink)  
Old November 12th, 2011, 10:15 PM
Authorized User
Points: 53, Level: 1
Points: 53, Level: 1 Points: 53, Level: 1 Points: 53, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2011
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by Paul_Turley View Post
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!
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Updating database record shrisangeeta Classic ASP Basics 4 February 28th, 2007 04:25 PM
Updating first found record rtr1900 Classic ASP Databases 9 December 2nd, 2005 02:09 AM
updating record stoneman Access 1 July 5th, 2005 09:12 AM
Record not updating Tangerine ASP.NET 1.x and 2.0 Application Design 3 March 24th, 2004 11:00 AM



All times are GMT -4. The time now is 04:05 AM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.