Debug SQL

Вставьте текст CREATE/CREATE OR ALTER/ALTER PROCEDURE — API ApplyProcLog вернёт процедуру с audit-логированием (sp_LogStart / sp_LogFinish).

Проект audit-базы и утилита применения аудита к процедурам: Moex_CGate / ProcDebug.

/*
--Тестовый вызов:
EXEC [dbo].[Proc_AllSqlTypes] @p_varchar='Логирование параметров вызова процедур.'

SELECT * FROM [audit].[LogProcedures]

-- Результат в логе, для быстрого вызова и отладки процедуры
EXEC [dbo].[Proc_AllSqlTypes] @p_bigint=NULL,@p_binary=NULL /*binary*/,@p_bit=NULL,
  @p_char=NULL,@p_date=NULL,@p_datetime=NULL,@p_datetime2=NULL,
  @p_datetimeoffset=NULL,@p_decimal=NULL,@p_float=NULL,
  @p_geography=NULL /*geography*/,@p_geometry=NULL /*geometry*/,@p_hierarchyid=NULL /*hierarchyid*/,
  @p_image=NULL /*image*/,@p_int=NULL,@p_money=NULL,@p_nchar=NULL,
  @p_ntext=NULL,@p_numeric=NULL,@p_nvarchar=NULL,@p_real=NULL,@p_smalldatetime=NULL,
  @p_smallint=NULL,@p_smallmoney=NULL,@p_sql_variant=NULL /*sql_variant*/,
  @p_sysname=NULL,@p_text=NULL,@p_time=NULL,@p_timestamp=NULL /*timestamp*/,
  @p_tinyint=NULL,@p_uniqueidentifier=NULL,@p_varbinary=NULL /*varbinary*/,
  @p_varchar='Логирование параметров вызова процедур.',@p_xml=NULL /*xml*/

*/
CREATE OR ALTER PROCEDURE [dbo].[Proc_AllSqlTypes]
    @p_bigint bigint = NULL,
    @p_binary binary(16) = NULL,
    @p_bit bit = NULL,
    @p_char char(10) = NULL,
    @p_date date = NULL,
    @p_datetime datetime = NULL,
    @p_datetime2 datetime2(7) = NULL,
    @p_datetimeoffset datetimeoffset(7) = NULL,
    @p_decimal decimal(18,4) = NULL,
    @p_float float = NULL,
    @p_geography geography = NULL,
    @p_geometry geometry = NULL,
    @p_hierarchyid hierarchyid = NULL,
    @p_image image = NULL,
    @p_int int = NULL,
    @p_money money = NULL,
    @p_nchar nchar(10) = NULL,
    @p_ntext ntext = NULL,
    @p_numeric numeric(18,4) = NULL,
    @p_nvarchar nvarchar(200) = NULL,
    @p_real real = NULL,
    @p_smalldatetime smalldatetime = NULL,
    @p_smallint smallint = NULL,
    @p_smallmoney smallmoney = NULL,
    @p_sql_variant sql_variant = NULL,
    @p_sysname sysname = NULL,
    @p_text text = NULL,
    @p_time time(7) = NULL,
    @p_timestamp timestamp = NULL,
    @p_tinyint tinyint = NULL,
    @p_uniqueidentifier uniqueidentifier = NULL,
    @p_varbinary varbinary(64) = NULL,
    @p_varchar varchar(100) = NULL,
    @p_xml xml = NULL
AS
BEGIN
  SET NOCOUNT ON;

BEGIN TRY
    -- outer try
    BEGIN TRY
      -- inner try
      IF @p_bit = 1
        RETURN;
      SELECT @p_int AS SampleInt;
    END TRY
    BEGIN CATCH
      -- inner catch
      THROW;
    END CATCH

IF @p_int = 0
      RETURN;
  END TRY
  BEGIN CATCH
    -- outer catch
    THROW;
  END CATCH
END

Оборачивать RETURN