Updating stored procedure

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

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

I am creating stored procedures for inserting and updating data in my SQL Server database.

At first I was creating a separate procedure for Add/Set but then I stumbled across a query that allows me to condense them into a single procedure.

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.

This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. By joining today you can post your own programming questions, respond to other developers questions, and eliminate the ads that are displayed to guests. This forum seemed like a party that died years ago!

Just keep in mind that only the primary key is used to guarantee uniqueness. I know in the real world this would be the wrong way but I for the sake of learning. 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 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 Thanks again.

In my Scenario, the First Name and Last names are primary Keys. In the real world, the name could be a company name, example: Best Buy.

I wanted to check with the SO community on any possible future issues doing it this way.

Separate Procedures --INSERT Procedure CREATE PROCEDURE [dbo].[Add Data Type] @Type Name [nvarchar](255), @Type Property [nvarchar](255) AS BEGIN SET NOCOUNT ON; INSERT INTO Data Type(Type Name, Type Property) VALUES(@Type Name, @Type Property) SELECT SCOPE_IDENTITY() END --UPDATE Procedure CREATE PROCEDURE [dbo].[Set Data Type] @ID [int], @Type Name [nvarchar](255), @Type Property [nvarchar](255) AS BEGIN SET NOCOUNT ON; UPDATE Data Type SET Type Name = @Type Name, Type Property = @Type Property WHERE ID = @ID END EXEC Add Data Type @Type Name = 'Test Name', @Type Property = 'Test Property' --INSERT EXEC Set Data Type @ID = 42, @Type Name = 'Test Name', @Test Property = 'Test Property' --UPDATE CREATE PROCEDURE [dbo].[Set Data Type] @ID [int] = NULL, @Type Name [nvarchar](255), @Type Property [nvarchar](255) AS BEGIN SET NOCOUNT ON; UPDATE Data Type SET Type Name = @Type Name, Type Property = @Type Property WHERE ID = @ID IF @@ROWCOUNT = 0 INSERT INTO Data Type(Type Name, Type Property) VALUES(@Type Name, @Type Property) IF @ID IS NULL SELECT SCOPE_IDENTITY() END EXEC Set Data Type @Type Name = 'New Type Name', @Type Property = 'New Type Property' --INSERT EXEC Set Data Type @ID = 42, @Type Name = 'Updated Type Name', @Type Property = 'Updated Type Property' --UPDATE So far I have 15 type tables that I creating procedures for and am trying to cut down on the number of procedures created, however I don't want to sacrifice performance.

Search for updating stored procedure:

updating stored procedure-78updating stored procedure-61updating stored procedure-53updating stored procedure-78

Leave a Reply

Your email address will not be published. Required fields are marked *

One thought on “updating stored procedure”

  1. After they gave me their blessing, our marriage is now in process and she will get the visa soon. You can find serious women here ready to stablish long term relationships.