EXEC sp_recompile sq_WSGetAppByLegId

DECLARE @dbname    nvarchar(256),
        @procname  nvarchar(256)
SELECT @dbname = 'AmbQueue',
       @procname = 'dbo.sq_WSGetAppByLegId'
 
; WITH basedata AS (
   SELECT qs.statement_start_offset/2 AS stmt_start,
          qs.statement_end_offset/2 AS stmt_end,
          est.encrypted AS isencrypted, est.text AS sqltext,
          epa.value AS set_options, qp.query_plan,
          charindex('<ParameterList>', qp.query_plan) + len('<ParameterList>')
             AS paramstart,
          charindex('</ParameterList>', qp.query_plan) AS paramend
   FROM   sys.dm_exec_query_stats qs
   CROSS  APPLY sys.dm_exec_sql_text(qs.sql_handle) est
   CROSS  APPLY sys.dm_exec_text_query_plan(qs.plan_handle,
                                            qs.statement_start_offset,
                                            qs.statement_end_offset) qp
   CROSS  APPLY sys.dm_exec_plan_attributes(qs.plan_handle) epa
   WHERE  est.objectid  = object_id (@procname)
     AND  est.dbid      = db_id(@dbname)
     AND  epa.attribute = 'set_options'
), next_level AS (
   SELECT stmt_start, set_options, query_plan,
          CASE WHEN isencrypted = 1 THEN '-- ENCRYPTED'
               WHEN stmt_start >= 0
               THEN substring(sqltext, stmt_start + 1,
                              CASE stmt_end
                                   WHEN 0 THEN datalength(sqltext)
                                   ELSE stmt_end - stmt_start + 1
                              END)
          END AS Statement,
          CASE WHEN paramend > paramstart
               THEN CAST (substring(query_plan, paramstart,
                                   paramend - paramstart) AS xml)
          END AS params
   FROM   basedata
)
SELECT set_options AS [SET], n.stmt_start AS Pos, n.Statement,
       CR.c.value('@Column', 'nvarchar(128)') AS Parameter,
       CR.c.value('@ParameterCompiledValue', 'nvarchar(128)') AS [Sniffed Value],
       CAST (query_plan AS xml) AS [Query plan]
FROM   next_level n
CROSS  APPLY   n.params.nodes('ColumnReference') AS CR(c)
ORDER  BY n.set_options, n.stmt_start, Parameter

public static List<Appointment> GetAppByLegId(long legId)
        {
            DBConnManager manager = DBConnManager.GetDBConnManager();
            IDbConnection conn = manager.GetConnection();
            List<Appointment> apps = null;
            DebugMessageService ds = DebugMessageService.GetService();

            try
            {
                OleDbDataReader dr = null;

                using (dr = DBUtils.ExecSQL(conn as OleDbConnection, null, "exec sq_WSGetAppByLegId ?", new object[] { legId }))
                {
                    apps = ParseDBResponce_Apps(dr);
                }
            }
            finally
            {
                manager.FreeConnection(conn);
            }
            return apps;
        }

...

        public IDbConnection GetConnection()
        {
            OleDbConnection conn = null;
            try
            {
                conn = new OleDbConnection(GetConnectionString());
            }
            catch (Exception e)
            {
                return null;
            }
            return conn;
        }

        public static OleDbDataReader ExecSQL(OleDbConnection conn, OleDbTransaction trans, string sql, object[] parameters)
        {
            OleDbCommand oleCmd = new OleDbCommand(sql);
            oleCmd.CommandTimeout = 300;
            oleCmd.Connection = conn;
            BuildParameters(oleCmd, parameters);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            if (trans != null)
                oleCmd.Transaction = trans;
            return oleCmd.ExecuteReader(CommandBehavior.Default);
        }

        public String NotifyLegStateChanged(long legId, ILog logger)
        {
            String responce = null;

            try
            {
                String brokerEndpoint = ConfigService.GetInstance().GetBrokerEndpoint();
                if (String.IsNullOrWhiteSpace(brokerEndpoint))
                {
                    throw new Exception("Broker Endpoint is empty");
                }

                List<Appointment> apps = DALApps.GetAppByLegId(legId);
                
                if (apps == null || apps.Count == 0)
                    throw new Exception("App for Leg with ID " + legId.ToString() + " not found");

                if (!apps.Any(x => x.Legs.Any(l => l.AppLegId == legId)))
                    throw new Exception("App Leg with ID " + legId.ToString() + " not found");

                Appointment app = apps.First(x => x.Legs.Any(l => l.AppLegId == legId));
                Models.AmbDispatch.Leg leg = app.Legs.First(l => l.AppLegId == legId);
                if (!apps[0].AppIsForToday())
                    return null;
                
                responce = DALBroker.GetInstance().NotifyLegUpdated(app, leg);
            }
            catch (Exception e)
            {
                responce = "Exception in DAL.NotifyAppStateChanged: " + e.Message;
            }
            
            return responce;
        }