1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Stored procedure for retrieving and update existing records

Discussion in 'SQL Server' started by becky.b, Feb 8, 2011.

  1. becky.b

    becky.b New Member

    Joined:
    Feb 8, 2011
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    0
    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!
     
  2. sql-programs

    sql-programs New Member

    Joined:
    Oct 21, 2009
    Messages:
    14
    Likes Received:
    2
    Trophy Points:
    0
    Occupation:
    Software Developer
    Home Page:
    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.
     
  3. sql-programs

    sql-programs New Member

    Joined:
    Oct 21, 2009
    Messages:
    14
    Likes Received:
    2
    Trophy Points:
    0
    Occupation:
    Software Developer
    Home Page:
    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
     
  4. vickya4n

    vickya4n New Member

    Joined:
    Aug 23, 2016
    Messages:
    15
    Likes Received:
    0
    Trophy Points:
    1
    Gender:
    Male
    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 ..
     
  5. Ami Desai

    Ami Desai Member

    Joined:
    Jan 5, 2017
    Messages:
    42
    Likes Received:
    17
    Trophy Points:
    8
    Location:
    Ahmedabad
    Home Page:
    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
     

Share This Page