Link to home
Start Free TrialLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

Capture executed SQL

Is there a way to capture what was last executed on a stored procedure?

Other that this... I have a table I just insert what was passed in
But I don't want to do this on every proc
  SET @CaptureExecSQL = 'EXEC [dbo].[ewUpdatePaymentsbyPartialRefund_Capture] @InvoiceID = ''' + @InvoiceID + ''' , @AmountRefunded = ''' + CAST(@AmountRefunded AS NVARCHAR(300)) + ''' , @AmountRefundedActual = ''' + CAST(@AmountRefundedActual AS NVARCHAR(300)) + ''' , @UserID = ''' +  CAST(@UserID AS VARCHAR(50))  + ''''
  INSERT INTO dbo.ewSQLSearch
       (
           SQLProcedure,
           SQLCode,
           DateAdded
       )
  VALUES
  (   'RefundCapture',       -- SQLProcedure - varchar(max)
      @CaptureExecSQL,       -- SQLCode - varchar(max)
      GETDATE() -- DateAdded - datetime
      )

Open in new window

 
Avatar of Jim Horn
Jim Horn
Flag of United States of America image
<Knee-jerk reaction with total air code>  If that SP returns a set, you can create a temp table whose table definition EXACTLY matches that set, and then do an INSERT...

CREATE TABLE #tmp (goo int, foo int, boo int) 

INSERT INTO #tmp (goo, foo, boo) 
exec SPthatreturnscolumnsgoofooboo

-- Looky here
SELECT * FROM #tmp

Open in new window


ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial