Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 250
  • Last Modified:

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

0
johnkainn
Asked:
johnkainn
2 Solutions
 
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now