Stored procedure - update return value

I have a table (MyTable) with 3 columns (Id, A, B).
I want to update if column A has the same value as the parameter @A sent into the Stored Procedure.
How do i return the value of Id column after a row has been updated?

@A varchar(max)
@B varchar(max)
AS
if exists(SELECT * FROM MyTable WHERE A=@A)
begin
	update MyTable SET B=@B WHERE A=@A
END

Open in new window

johnkainnAsked:
Who is Participating?
 
Pratima PharandeCommented:
try this
if Record exists it will return id otherwise 0

@A varchar(max)
@B varchar(max)
 
AS
Declare @ID int
Set @ID = 0
if exists(SELECT * FROM MyTable WHERE A=@A)
begin
	Select @id = ID ROM MyTable WHERE A=@A
 
	update MyTable SET B=@B WHERE A=@A
     
END
Return @ID

Open in new window

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
you should use the OUTPUT option, that will return all the IDs in case there exists many IDs with the same @A

DECLARE @Out table (Id int )

update MyTable
SET B=@B
OUTPUT inserted.ID into @Out
WHERE A=@A

IF @@Rowcount > 0
  SELECT ID from @Out
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.