- 001
- 002
- 003
- 004
- 005
- 006
- 007
- 008
- 009
- 010
- 011
- 012
- 013
- 014
- 015
- 016
- 017
- 018
- 019
- 020
- 021
- 022
- 023
- 024
- 025
- 026
- 027
- 028
- 029
- 030
- 031
- 032
- 033
- 034
- 035
- 036
- 037
- 038
- 039
- 040
- 041
- 042
- 043
- 044
- 045
- 046
- 047
- 048
- 049
- 050
- 051
- 052
- 053
- 054
- 055
- 056
- 057
- 058
- 059
- 060
- 061
- 062
- 063
- 064
- 065
- 066
- 067
- 068
- 069
- 070
- 071
- 072
- 073
- 074
- 075
- 076
- 077
- 078
- 079
- 080
- 081
- 082
- 083
- 084
- 085
- 086
- 087
- 088
- 089
- 090
- 091
- 092
- 093
- 094
- 095
- 096
- 097
- 098
- 099
- 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
Follow us!