Link to home
Create AccountLog 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
  (   '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

Open in new window

Avatar of lcohan
Flag of Canada image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account