Using Proc With Update Statement

• jchandramouli

  SSCommitted

  Points: 1772

  More actions

  February 17, 2009 at 4:35 am

  #97113

  Hi,

  How to use stored procedure with update statement.

  See the example below,

  CREATE PROC Temp

  (@chStatus CHAR(1))

  AS

  BEGIN

  SET @chStatus = 'E'

  END

  --

  Go

  --

  DECLARE @chStatus CHAR(1)

  DECLARE @tb TABLE(UD INT,Status CHAR(1))

  INSERT INTO @tb SELECT 1

  INSERT INTO @tb SELECT 2

  Update

  @tb

  Set

  Status = EXEC dbo.Temp @chStatus OUTPUT

  Now, from the last update statement you might have understood what i am looking for.

  The solution is could use a function for this process. But i need to do using proc.

  Thanks in advance.

  "I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  February 17, 2009 at 5:04 am

  #945090

  Assuming that you want to update status column with the output value of the variable @chStatus, then you have to do

  EXEC dbo.Temp @chStatus OUTPUT

  UPDATE @tb SET Status = @chStatus

  If that is not the case, if the procedure returns a resultset and of which the status is updated, the you have to do

  INSERT @tbltemp

  EXEC dbo.Temp @chStatus OUTPUT

  UPDATE t SET t.Status = tmp.Status

  FROM @tb t INNER JOIN @tbltemp tmp on t.SomeJoinCriteria = tmp.SomeJoinCriteria

  --Ramesh
  

  ---