Sql-server – How to use SQL Profiler to capture a single stored procedure

I'll bite the bullet and tell you that such a trace cannot be set up, because it is not the [perceived] purpose of traces. I have always done it this way:

WHILE (@@FETCH_STATUS = 0) AND
            ( @About80MoreBooleanExpressionsHere)
BEGIN /* 1 */
    -- about 700 lines of logic, math and if-parameter-this-then-that
    -- stuff omitted
      INSERT InnerProcedureCallWithinLoop__TraceTable
              VALUES (@from_locn, @About53PARAMSOMITTED
      EXEC @ConsiderItem =
            InnerProcedureCallWithinLoop
                  @from_locn,
                        @About53PARAMSOMITTED,
...

If I know that it is only ever called from one location. Otherwise, I do it in the callee instead of the caller.

ALTER PROC InnerProcedureCallWithinLoop
    @from_locn int,
    @About53PARAMSOMITTED ...
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;
INSERT InnerProcedureCallWithinLoop__TraceTable VALUES (@from_locn, @prm2, @prm3....
--- rest of proc

This is obviously different from using a trace, which is able to capture events even if they started and never finished (faulty parameters, rolled back transactions). If that is your problem, you need to look at CLR or email methods to externalise the captured output.

I did a DuckDuckGo.com search for the like_i_sql_unicode_string since there appears to be no documentation that describes it included with SQL Server, and came across this Connect item, https://connect.microsoft.com/SQLServer/feedback/details/699053/sql-batch-completed-event-returns-0-for-row-count#details - it contains a sample extended event in the "steps to reproduce" section of the Connect item. In the sample extended event is this text, which wraps the search terms in question in the standard SQL Server wildcard, %.

 WHERE ([sqlserver].[like_i_sql_unicode_string]([batch_text],N'%sys.objects%')))

I modified my extended event definition like:

DROP EVENT SESSION FindCaller ON SERVER;

CREATE EVENT SESSION [FindCaller] ON SERVER 
ADD EVENT sqlserver.sql_batch_starting
(
    ACTION
    (
        sqlserver.client_app_name
        , sqlserver.client_hostname
        , sqlserver.database_name
        , sqlserver.nt_username
        , sqlserver.session_id
        , sqlserver.sql_text
    )
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%ObjectInQuestion%'))
)
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
);

Now, when I 'Watch Live Data' I see the culprit statements, along with the actual SQL text being executed, the name of the application, the client machine name, etc. Quite useful.

The take-home for me is the Extended Event GUI needs better documentation!