dating black man advice - Updating a database stored procedure


updating a database stored procedure-5updating a database stored procedure-83

CREATE TABLE tbl Store( Store Name VARCHAR(30), Store Branch VARCHAR(30), CONSTRAINT pktbl Store PRIMARY KEY (Store Name, Store Branch), Store Address VARCHAR(30) NOT NULL, Store City VARCHAR(30) NOT NULL, Store State CHAR(2) NOT NULL, Store Zip CHAR(9) NOT NULL, Store Phone CHAR(10) NOT NULL, ); 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 sp Upd_Contact Name @First Name nvarchar(50) , @Last Name nvarchar(50) , @New First Name nvarchar(50) , @New Last Name nvarchar(50) as update My Contacts set First Name = @New First Name, Last Name = @New Last Name where First Name = @First Name and Last Name = @Last Name CREATE PROCEDURE sp Upd_Store Info @Store Name VARCHAR(30) , @Store Branch VARCHAR(30) , @Store Address VARCHAR(30) , @Store City VARCHAR(30) , @Store State CHAR(2) , @Store Zip CHAR(9) , @Store Phone CHAR(10) AS UPDATE tbl Store SET Store Address = @Store Address , Store City = @Store City , Store State = @Store State , Store Zip = @Store Zip , Store Phone = @Store Phone WHERE Store Name = @Store Name AND Store Branch = @Store Branch sp Upd_Store Info 'Maple Grove', 'Best Buy','99689 Ruggs Street', 'Minneapolis', 'MN', '55443','6122342345'; 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.

Example: Where company name = 'Best Buy' and Company Location = 'MPLS'.

This way we can update the address phone numbers but not the name.

However, I remain hopeful someone is still around to answer my question. Page 315 talks about performing updates using stored procedures. My second question is, how do I update a record without using a primary key? I beleive that when the stored procedure script was pasted into the manuscript, the last line was omitted. The code should read: CREATE PROCEDURE sp Upd_My Contacts @Contact ID int , @First Name nvarchar(50) , @Last Name nvarchar(50) , @Phone nvarchar(25) AS UPDATE My Contacts SET First Name = @First Name , Last Name = @Last Name , Phone = @Phone WHERE Contact ID = @Contact ID Regarding your second question, any column or combination of columns can be used in a WHERE clause to qualify records. 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.