0

Normally i will log this information at the end of each SP. The syntax looks like this.

DECLARE @SPContent nvarchar(4000)= (
            SELECT  @ServiceID [ServiceID],
                    @AccountID [FID],
                    @AccountName [Username],
                    @TopupAmount [TopupAmount],                 
                    @TopupDesc [Description],
                    @TransID [ReferenceID],
                    @SourceID [SourceID],                   
                    @TopupIP [ClientIP],                    
                    @AccountBalance [FunBalance],
                    @ResponseStatus_Deduct [ResponseStatus],
                    @Now [FromTime]
            FOR XML RAW)

            INSERT INTO [dbo].[InputLogs]
                ([SPName]
                ,[SPContent]
                ,[CreatedTime]
                ,[Username])
            SELECT  'SP_Balance_Deduct_Game',
                    @SPContent,
                    GETDATE(),
                    SUSER_NAME()

but i'm wondering if there is a optimal script to do this automatically where i only need to pass in the name of the SP as the only parameter

2 Answers 2

0

So i got to try it out and there is one problem/limitation: variables, there is as far as i know no way to dynamicaly and simply set a variating amount of parameters into a dynamic sql. So original idea is out. But, do not despair, there is a (possible) workaround.

I tried several approaches, but there is just no way , to dynamicaly read the variable names for dynamic SQL. IF anyone comes up with an answer for that, would be great.

This is one question that tries to tackle this limitation: explanation here

Sorry

Original answer:

I wont go into implementation details, but what i think you are looking for is a stored procedure utilizing dynamic sql to get the list of parameters and creating the xml on the fly....

For parameter list retrieval look here for example:

  1. get list of parameters from procedure based on procedure name
  2. use dynamic sql to construct the xml query based on this list
  3. call insert

You can put this all into one stored procedure and just call it ie

Exec dbo.storeprocedureparamd 'procedurename'

Note: actually cant, as the variables would not be visible in the logging SP.

You can try to do it even more copypaste and use this

3
  • One point i ma not sure how it eould work IF relevant for you, is table variables behaviour.... But thats still doable, just needs handling Nov 4 at 11:55
  • The ideas was great and straight forward. But im currently stuck at one point trying to figure out the dynamic way. I can easily get the parameter from SP (@var1, @var2 for example). But i when i construct the query like Exec( @query) with @query = 'Select' +'( @var1, @var 2)' +'(into log table)' It said that i have to define the parameter again although i have try and test to declare var1 and var2 at the top Which mean that the dynamic SQL query inside EXEC cant not take the parameter that i pass in outside of the EXEC statement. Am i missing something Nov 4 at 13:00
  • @TạĐức i am now afk but make the variables part of the string itself, i think the simplest way would be to have the whole solution as dynamic sql, including the insert to avoid variable issues Nov 4 at 13:27
0

You should probably consider using an XEvent trace for this.

The statement field will contain the procedure being called, along with its parameters.

CREATE EVENT SESSION [ProcedureExecution] ON SERVER 
ADD EVENT sqlserver.rpc_starting
(
  SET collect_statement = (1)
  ACTION
  (
    sqlserver.client_hostname,
    sqlserver.database_name,
    sqlserver.username
  )
)
ADD EVENT sqlserver.sql_batch_starting
(
  ACTION
  (
    sqlserver.client_hostname,
    sqlserver.database_name,
    sqlserver.username
  )
)
ADD TARGET package0.event_file
(
    SET filename = N'YourFileHere',
    max_file_size=(10)
)
WITH
(
  EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY = 5 SECONDS
);
GO

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.