Retrive and update existing record from database

Question

Hi I am using MS SQL Server 2008 to create a stored procedure that is responsible for retrieving and updating existing record from database. However it does not update the records. I am not sure of what is wrong with the stored procedure. This is the stored procedure that I have input: ALTER PROCEDURE [OS].[UPDATE_ItemsRecords] @itemID varchar (10) @itemName varchar (1000) @itemDesc varchar (10) AS Select @itemName = itemName @itemDesc = itemDesc from OS .Items WHERE itemID = @itemID UPDATE OS .Items SET itemName = @itemName  itemDesc = @itemDesc WHERE itemID = @itemID I am unable to retrive and update the existing records. How do I resolve it?Thks!

Becky Bloomwood · Answer

Sure thanks:)

Becky Bloomwood · Answer

Thanks. For this update stored procedure I hope that when user clicks on the Edit link they are able to retreive back the record entered previously and entered in a new one to update the existing record to the latest. ALTER PROCEDURE [OS].[UPDATE_ItemsRecords] @itemID varchar (10) @itemName varchar (1000) @itemDesc varchar (10) AS UPDATE OS .Items SET itemName = @itemName  itemDesc = @itemDesc WHERE itemID = @itemID I have modify to become the one that you guys suggested but the records still remains the same. Thanks!

Suthish Nair · Answer

just write the update statement.. UPDATE OS.Items SET itemName = @itemName  itemDesc = @itemDesc WHERE itemID = @itemID why you need a select query. what kind of logic you want to apply. you want to retrive history records or something else. provide more information so we can help.

Micke Blomquist · Answer

If you open a new query window in your database and run this query: Select itemName itemDesc from OS.Items WHERE itemID = @itemID Replacing @itemID with the same parameter as from your C# code do you get a hit on an item? (Perhaps you need to trim your variable taking away spaces???)

Becky Bloomwood · Answer

I uncomment the select stmt and run it. But the records is still not updated::(

Micke Blomquist · Answer

Hi Becky Just as Krishna says you are just replacing the the existing values. Try to comment the select clause just running the update part. The select clause is what is giving you a hard time!

Becky Bloomwood · Answer

But how do we make use of 2 stored procedure and link them together? Cause at the business logic there I only defined one method for retrieving and updating the existing records.

Krishna Garad · Answer

Just check your Select Statement what you are doing you are just selecting existing value and the same value you are using for update statement that's it is not updating record pass new values. Instead of single sp use two stored procedure one for select and second for update.