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!
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.
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
We can update records of the table(s) using stored procedure by passing data in input parameters. Below code is used to update a table ..
HI, You can check this Code: To Update CREATE PROCEDURE usp_UpdateEmployee @flag bit output,-- return 0 for fail,1 for success @EmpID int, @Salary int, @Address varchar(100) AS BEGIN BEGIN TRANSACTION BEGIN TRY Update Employee set Salary=@Salary, Address=@Address Where EmpID=@EmpID set @flag=1; IF @@TRANCOUNT > 0 BEGIN commit TRANSACTION; END END TRY BEGIN CATCH IF @@TRANCOUNT > 0 BEGIN rollback TRANSACTION; END set @flag=0; END CATCH END To Retrieve Updated Record Declare @flag bit EXEC usp_UpdateEmployee @flag output,1,22000,'Noida' if @flag=1 print 'Successfully updated' else print 'There is some error' Thanks