sqldatabasestored-proceduressql-updatedistributed-database

how to return updated and previous value through a stored procedure?


help needed regarding an assignment: Write a stored procedure, which takes EID and CITY as an argument, and update city of the employee of given EID. After updating city, you must display old city and new city of employee along with his/her name.I tried the following which is executing half part successfully

`create proc latest(@eid int, @city varchar(10))

AS

Begin

update employee set city=@city where @eid=eid

End

exec latest 12, 'rawalpindi'`


Solution

  • Store the original value before you update it. Something like:

    declare @previouscity varchar(10)
    set @previouscity = (select city from employee where eid=@eid)
    

    Then you do the update and the last thing is something like this:

    select name + ' changed city from ' + @previouscity + ' to ' + city
    from employee
    where eid=@eid
    

    all untested