Updating a database stored procedure Free willing teen chat

Posted by / 07-Aug-2016 08:09

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.

Here is the piece of code I am referring to: 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 sp Upd_My Contacts 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. Can I specify other attributes in a where clause rather then using a primary key? Just keep in mind that only the primary key is used to guarantee uniqueness. 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. Example: Update record is First name = paul and last name = Turley.

All you need to do is convert your data or columns to XML.

By putting a SELECT statement immediately before the UPDATE (and assuming no one else is accessing the same data at teh same moment) you'll see if there is anything to actually UPDATE.Can anyone see where I might be going wrong or tell me why my output messages are lying to me? That way you can capture what is happening in the INSERT/UPDATE statement Here is the sample code: Sql Server2005/2008 // OUTPUT clause in INSERT/UPDATE/DELETE statements These types of samples are all over the place on the web, but here is my original example for which I believe is better clarity. UPDATE: Just tried a different approach, same results, just 3 more ALTER PROCEDURE update_rent AS DECLARE @tmprent TABLE (hmy INT, rent decimal(11,2)); DECLARE @flag INT SET @flag = (select COUNT(*) from unit_rent left outer join unittype on unittype = scode where rent srent) WHERE hmy = 1 UPDATE unittype SET srent = (select rent from @tmprent where hmy = 1) WHERE = (select min(hmy) from unittype left outer join unit_rent on unittype = scode where rent EDIT--------------------------------------- If you really want to see what is happening, then code up some "output" auditing......Example: Where company name = 'Best Buy' and Company Location = 'MPLS'.This way we can update the address phone numbers but not the name.

updating a database stored procedure-44updating a database stored procedure-64updating a database stored procedure-9

One thought on “updating a database stored procedure”

  1. Most were apparently designed by an 8-year-old with a Mac from 1992, back when animated GIFs were cool and a rotating smiley was the pinnacle of innovation.) The gist: This one seems reputable, if a bit skewed toward 40-year-olds who like swimming with the dolphins. The bad: Non-paying members can only send one of 13 short, canned messages, like “I feel a nice connection with you after reading your profile.” The bizarre: Verdict: There are only seven guys in Seattle between the ages of 25 and 35 whose profiles include a photo.