Retrive and update existing record from database

Becky Bloomwood

15y15k

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!

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Becky Bloomwood15y ago
Sure, thanks:)
0
Becky Bloomwood15y ago
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!
0
Suthish Nair15y ago
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.
0
Micke Blomqvist15y ago
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???)
0
Becky Bloomwood15y ago
I uncomment the select stmt and run it. But the records is still not updated::(
0
Micke Blomqvist15y ago
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!
0
Becky Bloomwood15y ago
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.
0
Krishna Garad15y ago
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.
0