-1

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

ashiq pervez
  • 45
  • 1
  • 2
  • 15

2 Answers2

1

Use the output clause of the update statement.

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

I'm not going to write the exact output statement for you. Do your homework! :)

allmhuran
  • 4,154
  • 1
  • 8
  • 27
  • how would i be accessing previous latest value in a the table witrout LAG() or Lead() function – ashiq pervez Jul 16 '20 at 18:50
  • `Lag` and `lead` are for finding rows in the current data, based on their relative position ordered by some column. So if I have a column with the numbers 1, 2, and 3 in it, I can get the value 1 by taking `lag()` of the row with the value 2. What you want isn't "previous by position in the table". You want "previous by *time in the real world*". What did this table have in it a moment ago, before I did the update? The output clause can give you this. If you click the link in my message it will take you to the documentation on how to use it. There are some good tutorials online as well. – allmhuran Jul 16 '20 at 19:33
0

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

avery_larry
  • 2,069
  • 1
  • 5
  • 17