1. 1
2. 2
3. 3
4. 4
5. 5
6. 6
7. 7
8. 8
9. 9
10. 10
11. 11
12. 12
13. 13
14. 14
15. 15
16. 16
17. 17
18. 18
19. 19
20. 20
21. 21
22. 22
23. 23
24. 24
25. 25
26. 26
27. 27
28. 28
29. 29
30. 30
31. 31
32. 32
33. 33
34. 34
35. 35
36. 36
37. 37
38. 38
39. 39
40. 40
41. 41
42. 42
43. 43
44. 44
45. 45
46. 46
47. 47
48. 48
49. 49
50. 50
51. 51
52. 52
53. 53
54. 54
55. 55
56. 56
57. 57
58. 58
59. 59
60. 60
61. 61
62. 62
63. 63
64. 64
65. 65
66. 66
67. 67
68. 68
69. 69
70. 70
71. 71
72. 72
73. 73
74. 74
75. 75
76. 76
77. 77
78. 78
79. 79
80. 80
81. 81
82. 82
83. 83
84. 84
85. 85
86. 86
87. 87
88. 88
89. 89
90. 90
91. 91
92. 92
93. 93
94. 94
95. 95
96. 96
97. 97
98. 98
99. 99
100. 100

CREATE PROCEDURE [dbo].[DogovorZalog_Get]
   @idZalog         AS INT                    --По какому залогу требуется вывести информацию
  ,@Result          AS SMALLINT       = 0 OUT --Результат выполнения: 1=Выполнено успешно
  ,@ErrorComm       AS NVARCHAR(2000) = N'' OUT --Текст ошибки/результата
  ,@ErrorCode       AS INT            = NULL OUT --Код ошибки
  ,@needSelect      AS BIT            = 0 --1=Будет вывод результата в select
  ,@needRaisError   AS SMALLINT       = 0  --(1=Будет вызов RaisError наружу)
  ,@needSelectInfo  AS SMALLINT       = NULL --(Делать информационный селект или нет. NULL= возьмет из @needSelect)
  ,@debug           AS BIT            = 0 --1=Вывод отладочной информации
  ,@Message         AS NVARCHAR(2000) = N'' OUT --Сообщение для пользователя

AS 
BEGIN
  SET DATEFORMAT DMY;
  SET NOCOUNT ON;
  SET @Result = 0
  SET @ErrorComm = N''
  DECLARE @ProcName       AS SYSNAME        = OBJECT_NAME(@@PROCID, DB_ID())
         ,@tranCnt        AS INT            = 0 --(уровень вложенности транзакции)
         ,@isTran         AS SMALLINT       = 0 --(1=используется транзакция)
         ,@needTran       AS SMALLINT       = 0 --(1=Будет все в транзакции)
--=========================================
  BEGIN TRY
  SET @tranCnt = @@TRANCOUNT
  IF @tranCnt = 0 AND @needTran <> 0 SET @isTran = 1 
  IF @isTran = 1 BEGIN TRAN
  IF @needSelectInfo IS NULL SET @needSelectInfo = @needSelect

  --region Логирование
  SET @ExProc=CONCAT('EXEC ', @ProcName , CHAR(10),
    ' @idZalog=', CONVERT(NVARCHAR(50),@idZalog),CHAR(10),
    ',@Result=', CONVERT(NVARCHAR(50),@Result),CHAR(10),
    ',@ErrorComm=', '''', REPLACE(@ErrorComm,'''',''''''), '''',CHAR(10),
    ',@ErrorCode=', CONVERT(NVARCHAR(50),@ErrorCode),CHAR(10),
    ',@needSelect=', CONVERT(NVARCHAR(50),@needSelect),CHAR(10),
    ',@needRaisError=', CONVERT(NVARCHAR(50),@needRaisError),CHAR(10),
    ',@needSelectInfo=', CONVERT(NVARCHAR(50),@needSelectInfo),CHAR(10),
    ',@debug=', CONVERT(NVARCHAR(50),@debug),CHAR(10),
    ',@Message=', '''', REPLACE(@Message,'''',''''''), '''')

  --endregion Логирование

  SELECT TOP 1 *
  FROM dbo.Dogovor_Zalog AS DZ
    LEFT JOIN dbo.ZalogDestroy AS ZD ON DZ.idDestroy = ZD.idDestroy
  WHERE DZ.ID = @idZalog
  ORDER BY zd.keyDZ DESC
    
  SET @Result=1
  IF @isTran=1 COMMIT TRAN  
  END TRY
  BEGIN CATCH
  --region Обработчик ошибок
    SET @Result = 0
    --(описание деталей ошибки для внешнего обработчика)
    SET @ErrorComm = LEFT(N'Ошибка: ' +
        CASE WHEN @ErrorComm<>N'' THEN @ErrorComm + CHAR(10) ELSE N'' END  
        + ISNULL(N',@Message:' + CAST(@Message AS NVARCHAR(200)) + CHAR(10), N'')
        , 2000)

    --(описание ошибки для внутреннего обработчика)
    SET @ErrorCommInner = LEFT(
        CASE WHEN @ErrorComm<>N'' THEN @ErrorComm + CHAR(10) ELSE N'' END  
        + N'SP:'         + ISNULL(ERROR_PROCEDURE(), N'')                   + CHAR(10)
        + N',Line:'      + ISNULL(CAST(ERROR_LINE() AS NVARCHAR), N'')      + CHAR(10)
        + N',Severity:'  + ISNULL(CAST(ERROR_SEVERITY() AS NVARCHAR), N'')  + CHAR(10)
        + N',Error:'     + ISNULL(CAST(ERROR_NUMBER() AS NVARCHAR), N'')    + CHAR(10)
        + N',Message:'   + ISNULL(ERROR_MESSAGE(), N'')                     + CHAR(10)
        + N',State:'     + ISNULL(CAST(ERROR_STATE() AS NVARCHAR), N'')     + CHAR(10)
        + ISNULL(N','    + @ErrorCommInner, N'') + N';', 2000)
    PRINT @ErrorCommInner 
    
    IF @isTran = 1
      BEGIN
        /*IF XACT_STATE()=-1*/ ROLLBACK TRAN
      /*IF XACT_STATE()=1  COMMIT TRAN*/
      END

    IF @needRaisError <> 0
    BEGIN
      DECLARE @ErrorSeverityOut INT     = ERROR_SEVERITY()
             ,@ErrorStateOut    TINYINT = ERROR_STATE();

      THROW 50000, @ErrorComm, 1
    END
  --endregion Обработчик ошибок
  END CATCH
mEx:
  IF @needSelect <> 0
  BEGIN
    IF @needSelectInfo <> 0
      SELECT @Result AS 'Res'
            ,@ErrorComm AS 'ResTxt'
            ,@ErrorCode AS 'ErrorCode'
            ,@Message AS 'Message'
            ,@ErrorSeverity AS 'Severity'

  END
  RETURN
END