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
     

Share This Page