Cómo agregar un Try / Catch al Procedimiento almacenado SQL
CREATE PROCEDURE [dbo].[PL_GEN_PROVN_NO1]
@GAD_COMP_CODE VARCHAR(2) =NULL,
@@voucher_no numeric =null output
AS
BEGIN
DECLARE @NUM NUMERIC
DECLARE @PNO NUMERIC
SET @PNO = 0
DECLARE @PNO1 NUMERIC
SET @PNO1=0
-- begin transaction
IF NOT EXISTS (select GLDC_NEXT_PRV_NO
FROM GLAS_FINANCIAL_DOCUMENTS
WHERE GLDC_COMP_CODE = @GAD_COMP_CODE
AND GLDC_DOC_CODE = 'JV' )
BEGIN
RAISERROR ('Error in generating provision number..',16,1)
-- ROLLBACK TRANSACTION
END
ELSE
SELECT @PNO=ISNULL(GLDC_NEXT_PRV_NO,0)+1
FROM GLAS_FINANCIAL_DOCUMENTS
WHERE GLDC_COMP_CODE = @GAD_COMP_CODE
AND GLDC_DOC_CODE = 'JV'
UPDATE GLAS_FINANCIAL_DOCUMENTS
SET GLDC_NEXT_PRV_NO = @PNO
WHERE GLDC_COMP_CODE = @GAD_COMP_CODE
AND GLDC_DOC_CODE = 'JV'
set @@VOUCHER_NO=@PNO
--commit transaction
END
En este proceso ¿cómo puedo manejar try catch for exception?
26
4 answers
Ver aquí
CREATE PROCEDURE [dbo].[PL_GEN_PROVN_NO1]
@GAD_COMP_CODE VARCHAR(2) =NULL,
@@voucher_no numeric =null output
AS
BEGIN
begin try
-- your proc code
end try
begin catch
-- what you want to do in catch
end catch
END -- proc end
31
Author: Preet Sangha,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-09-26 13:25:33
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-09-26 13:25:33
Transact-SQL es un poco más complicado que los bloques try/catch de C# o C++, debido a la complejidad añadida de las transacciones. Un bloque CATCH tiene que comprobar la función xact_state () y decidir si puede confirmar o tiene que revertir. He cubierto el tema en mi blog y tengo un artículo que muestra cómo manejar correctamente las transacciones con un bloque try catch, incluidas las posibles transacciones anidadas: Manejo de excepciones y transacciones anidadas.
create procedure [usp_my_procedure_name]
as
begin
set nocount on;
declare @trancount int;
set @trancount = @@trancount;
begin try
if @trancount = 0
begin transaction
else
save transaction usp_my_procedure_name;
-- Do the actual work here
lbexit:
if @trancount = 0
commit;
end try
begin catch
declare @error int, @message varchar(4000), @xstate int;
select @error = ERROR_NUMBER(),
@message = ERROR_MESSAGE(), @xstate = XACT_STATE();
if @xstate = -1
rollback;
if @xstate = 1 and @trancount = 0
rollback
if @xstate = 1 and @trancount > 0
rollback transaction usp_my_procedure_name;
raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
return;
end catch
end
27
Author: Remus Rusanu,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-09-26 16:10:28
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2009-09-26 16:10:28
Sí, incluso puedes anidar las declaraciones try catch como:
BEGIN TRY
SET @myFixDte = CONVERT(datetime, @myFixDteStr,101)
END TRY
BEGIN CATCH
BEGIN TRY
SET @myFixDte = CONVERT(datetime, @myFixDteStr,103)
END TRY
BEGIN CATCH
BEGIN TRY
SET @myFixDte = CONVERT(datetime, @myFixDteStr,104)
END TRY
BEGIN CATCH
SET @myFixDte = CONVERT(datetime, @myFixDteStr,105)
END CATCH
END CATCH END CATCH
3
Author: Chagbert,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-06-02 12:53:15
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-06-02 12:53:15
Create Proc[usp_mquestions]
(
@title nvarchar(500), --0
@tags nvarchar(max), --1
@category nvarchar(200), --2
@ispoll char(1), --3
@descriptions nvarchar(max), --4
)
AS
BEGIN TRY
BEGIN
DECLARE @message varchar(1000);
DECLARE @tempid bigint;
IF((SELECT count(id) from [xyz] WHERE title=@title)>0)
BEGIN
SELECT 'record already existed.';
END
ELSE
BEGIN
if @id=0
begin
select @tempid =id from [xyz] where id=@id;
if @tempid is null
BEGIN
INSERT INTO xyz
(entrydate,updatedate)
VALUES
(GETDATE(),GETDATE())
SET @tempid=@@IDENTITY;
END
END
ELSE
BEGIN
set @tempid=@id
END
if @tempid>0
BEGIN
-- Updation of table begin--
UPDATE tab_questions
set title=@title, --0
tags=@tags, --1
category=@category, --2
ispoll=@ispoll, --3
descriptions=@descriptions, --4
status=@status, --5
WHERE id=@tempid ; --9 ;
IF @id=0
BEGIN
SET @message= 'success:Record added successfully:'+ convert(varchar(10), @tempid)
END
ELSE
BEGIN
SET @message= 'success:Record updated successfully.:'+ convert(varchar(10), @tempid)
END
END
ELSE
BEGIN
SET @message= 'failed:invalid request:'+convert(varchar(10), @tempid)
END
END
END
END TRY
BEGIN CATCH
SET @message='failed:'+ ERROR_MESSAGE();
END CATCH
SELECT @message;
0
Author: Harikesh Yadav,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-08-05 06:12:50
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2016-08-05 06:12:50