Stored procedure for retrieving and update existing records

becky.b's Avatar, Join Date: Feb 2011
Newbie Member
Hi, I am doing a update stored procedure that allows me to retrieve and update exisiting records from database. However, records entered previously cannot be retreived, hence unable to update it.

This is the code for it:
Code:
 
ALTER PROCEDURE [OS].[UPDATE_ItmsRecords] 
@itemName varchar(100),
@itemDesc varchar(1000),
@itemCat varchar(100)
AS

Select @itemName  = itemName , @itemDesc = itemDesc, @itemCat  = itemCat  WHERE itemName = @itemName
 UPDATE OS.Items SET itemName = @itemName , itemDesc = @itemDesc, itemCat = @itemCat WHERE itemName = @itemName
How do I resolve it such that it can retrieve previous record and update it to the database(MS SQL Server 2008)
Thanks!
sql-programs's Avatar, Join Date: Oct 2009
Go4Expert Member
Hi,

If u retrieve the previously entered records use the
SELECT IDENT_CURRENT(‘table name’)
is used the last inserted record id value from particular table.After that u can update the particular record with new values.
sql-programs's Avatar, Join Date: Oct 2009
Go4Expert Member
ALTER PROCEDURE [OS].[UPDATE_ItmsRecords]
@itemName varchar(100),
@itemDesc varchar(1000),
@itemCat varchar(100)
AS

DECLARE @Validate Varchar(200)
SET @Validate=(Select itemName = @itemName WHERE itemName = @itemName)
IF(@Validate <> '')
UPDATE OS.Items SET itemName = @itemName , itemDesc = @itemDesc, itemCat = @itemCat WHERE itemName =
@itemName
ELSE
PRINT 'No Records'


Just You try it. I think it may be work properly