Some questions about Stored Procedures, Error Handling,Tra..

Hi, I have a stored procedure like this:


---------------------------------------------------------
PROCEDURE dbo.SPGravaAgendaProf
@CodProf int,
@Usuario uniqueidentifier,
@CodEspec int,
@Dt_Inicio date,
@Dt_Fim date,
@Hora_Inicio time(7),
@Qtd int,
@Intervalo time(7),
@Err nvarchar(2048) output
AS
begin tran
BEGIN try
declare @id_agenda int;
insert into Agendas (Cod_Tipo_Agenda) values (1);
select @id_agenda = SCOPE_IDENTITY();
INSERT INTO Agendas_Profissional
(Cod_Agenda, Cod_Especialidade, Cod_Profissional,
Dt_Inicio, Dt_Fim, Hora_Inicio, Qtd, Intervalo, Ativa)
VALUES (@Id_Agenda,@CodEspec,@CodProf,@Dt_Inicio,
@Dt_Fim,@Hora_Inicio,@Qtd,@Intervalo,0);
INSERT INTO Log_Agendas
(Id_Agenda, Dt_Hora, usuario, Id_Tp_Entrada)
VALUES (@id_agenda,GETDATE(),@Usuario,1)
commit;
return 1;
end try
begin catch
select @err = error_message();
rollback;
RETURN 0;
end catch;
-------------------------------------------------------

And I have some questions, and hope that you guys can help me...

1 - Is this the best way to handle error and return the error to the
application?

2 - The transaction is right? I'm having an issue that when the first
insert goes ok and the second or third goes wrong, the identity column
of the Agenda table is incremented, and then when I insert another row,
it jumps to the next identity number, and stay a hole between number,
like 1,3,4,6....

3 - If I create a Stored Procedure to handle the insert of the log table
and call this procedure instead of the third insert, will this sp be in
the transaction of this procedure?

Thanks...


*** Sent via Developersdex http://www.developersdex.com ***

On Dec 19, 5:31 am, Ricardo Luceac wrote:
> Hi, I have a stored procedure like this:
>
> ---------------------------------------------------------
> PROCEDURE dbo.SPGravaAgendaProf
>         @CodProf int,
>         @Usuario uniqueidentifier,
>         @CodEspec int,
>         @Dt_Inicio date,
>         @Dt_Fim date,
>         @Hora_Inicio time(7),
>         @Qtd int,
>         @Intervalo time(7),
>         @Err nvarchar(2048) output      
> AS
> begin tran
> BEGIN try
>         declare @id_agenda int;
>         insert into Agendas (Cod_Tipo_Agenda) values (1);
>         select @id_agenda = SCOPE_IDENTITY();
>         INSERT INTO Agendas_Profissional
>                 (Cod_Agenda, Cod_Especialidade, Cod_Profissional,
>         Dt_Inicio, Dt_Fim, Hora_Inicio, Qtd, Intervalo, Ativa)
>                 VALUES (@Id_Agenda,@CodEspec,@CodProf,@Dt_Inicio,
>                 @Dt_Fim,@Hora_Inicio,@Qtd,@Intervalo,0);
>         INSERT INTO Log_Agendas
>         (Id_Agenda, Dt_Hora, usuario, Id_Tp_Entrada)
>                 VALUES (@id_agenda,GETDATE(),@Usuario,1)        
>         commit;
>         return 1;
> end try
> begin catch
>         select @err = error_message();
>         rollback;
>         RETURN 0;
> end catch;
> -------------------------------------------------------
>
> And I have some questions, and hope that you guys can help me...
>
> 1 - Is this the best way to handle error and return the error to the
> application?
>
> 2 - The transaction is right? I'm having an issue that when the first
> insert goes ok and the second or third goes wrong, the identity column
> of the Agenda table is incremented, and then when I insert another row,
> it jumps to the next identity number, and stay a hole between number,
> like 1,3,4,6....

There is no easy way to get rid of gaps.

>
> 3 - If I create a Stored Procedure to handle the insert of the log table
> and call this procedure  instead of the third insert, will this sp be in
> the transaction of this procedure?
>
Yes it will.

excellent artcile:

http://www.sommarskog.se/error-handling-I.html

the convention is to return 0 on success, errror code on failure.