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

Question

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'`

Answer 1

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