All we need is an easy explanation of the problem, so here it is.
How do you capture executions for a given stored procedure along with the passed parameter values?
I tried using sqlserver.module_end but it only shows the store proc execution with no param values:
CREATE EVENT SESSION [SP Executions By Name] ON SERVER ADD EVENT sqlserver.module_end(SET collect_statement=(1) ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.query_hash_signed,sqlserver.session_id,sqlserver.sql_text,sqlserver.username) WHERE ([object_type]='P ' AND [sqlserver].[database_name]=N'MyDB' AND [object_name]=N'MyStoredProc')) ADD TARGET package0.event_file(SET filename=N'Z:\Extended_Events\StoredProcedures_ByNameAndDB.xel',max_file_size=(5),max_rollover_files=(5)) WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
This is possible only if your proc is the oproc submitted in batch. If this is case, use rpc_completed or sql_batch_completed and filter with like predicate
Nested procs is notnpossible see parameters values.
Consider logging the parameters in a table for analsis.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂