 |
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|

November 12th, 2011, 06:31 PM
|
Authorized User
|
|
Join Date: Mar 2011
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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 06:54 PM..
|

November 12th, 2011, 08:48 PM
|
Wrox Author
|
|
Join Date: May 2004
Posts: 61
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

November 12th, 2011, 09:12 PM
|
Authorized User
|
|
Join Date: Mar 2011
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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!
|

November 12th, 2011, 09:20 PM
|
Wrox Author
|
|
Join Date: May 2004
Posts: 61
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

November 12th, 2011, 09:32 PM
|
Authorized User
|
|
Join Date: Mar 2011
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
Last edited by NovicePGM2011; November 12th, 2011 at 09:35 PM..
|

November 12th, 2011, 09:55 PM
|
Authorized User
|
|
Join Date: Mar 2011
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|

November 12th, 2011, 11:15 PM
|
Authorized User
|
|
Join Date: Mar 2011
Posts: 12
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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!
|

October 20th, 2015, 02:21 PM
|
Registered User
|
|
Join Date: Oct 2015
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
@ symbol in Stored Procedure
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.
|
|
 |