Handle Error question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Handle Error question

Author

Topic

shifis
Posting Yak Master

157 Posts

Posted - 2006-03-13 : 15:05:36

I have the next sp:

CREATE PROCEDURE SP_SALDO
(
@strDatos char(50)
)

AS

declare @ID char(20)
declare @Monto decimal(9,2)
declare @SaldoActual decimal(9,2)
DECLARE @TipMov tinyint
declare @Fecha char(11)
declare @Hora char(10)
declare @Tienda tinyint
declare @NoCaja tinyint
declare @SaldoNuevo decimal(9,2)
declare @NoAuto int
DECLARE @NomEmp char(40)
DECLARE @Trans_Num numeric

set nocount on

--SET @strDatos='T23C02001000000000000000000020025047'

SET @ID= SUBSTRING(@strDatos , 10, 20 )
set @Monto= cast(SUBSTRING(@strDatos , 30, 7 ) as decimal(9,2))/100
set @TipMov= cast(SUBSTRING(@strDatos , 7, 3 ) as int)
set @Tienda= cast(SUBSTRING(@strDatos , 2, 2 ) as int)
set @NoCaja= cast(SUBSTRING(@strDatos , 5, 2 ) as int)
set @Fecha = convert(char(11), getdate(), 101)
set @Hora= CONVERT(VARCHAR,getdate(),108)
set @NoAuto=ABS(CHECKSUM(NewID())) % 100000 + 1

--Se guarda el monto Actual y el nombre en variables
SElect @SaldoActual = SALDO, @NomEmp = NOM_EMP, @Trans_Num=last_trans_num +1
FROM TBSALDOS
WHERE ID=@ID
--select @SaldoActual,@ID,@Monto,@TipMov,@Tienda, @NoCaja, @Fecha, @Hora,@NoAuto

if (@Monto<=@SaldoActual)
begin
set @SaldoNuevo=@SaldoActual-@Monto

insert TBMOVIMIENTOS
values(@trans_num,@ID,@TipMov,GETDATE(),@hora,@Tienda,@NoCaja,@SaldoActual,@Monto,@NoAuto)

Update TBSALDOS
SET SALDO=@SaldoNuevo, SALDO_ULT_MOV=@Monto,
DT_ULT_MOV=GETDATE(), HR_ULT_MOV=@Hora,
NO_TIE_ULT_MOV=@Tienda, NO_CAJA_ULT_MOV=@NoCaja,
last_trans_num=@Trans_Num
WHERE ID=@ID

SELECT 'OK'+ SUBSTRING(@strDatos , 1, 6 )+@id
+RIGHT(REPLICATE('0',7) + CAST(cast(@SaldoNuevo*100 as int) as varchar(10)) ,7)
+ RIGHT(REPLICATE(' ',40) +@NomEmp ,40)
+ + RIGHT(REPLICATE('0',6) + CAST(cast(@NoAuto as int) as varchar(10)) ,6) as resp
end
else
begin
SELECT 'NO'+ SUBSTRING(@strDatos , 1, 6 )+@id
+RIGHT(REPLICATE('0',7) + CAST(cast(@SaldoActual*100 as int) as varchar(10)) ,7)
+ RIGHT(REPLICATE(' ',40) +@NomEmp ,40)
+ '000000' as resp
end
GO

How can I add a handle error?

What I need to do is if you can not do the insert and the update returns :

SELECT 'NO'+ SUBSTRING(@strDatos , 1, 6 )+@id
+RIGHT(REPLICATE('0',7) + CAST(cast(@SaldoActual*100 as int) as varchar(10)) ,7)
+ RIGHT(REPLICATE(' ',40) +@NomEmp ,40)
+ '000000' as resp
end

shifis
Posting Yak Master

157 Posts

Posted - 2006-03-13 : 17:09:30

Do you think this is gonna work?

CREATE PROCEDURE SP_SALDO
(
@strDatos char(50)
)

AS

declare @ID char(20)
declare @Monto decimal(9,2)
declare @SaldoActual decimal(9,2)
DECLARE @TipMov tinyint
declare @Fecha char(11)
declare @Hora char(10)
declare @Tienda tinyint
declare @NoCaja tinyint
declare @SaldoNuevo decimal(9,2)
declare @NoAuto int
DECLARE @NomEmp char(40)
DECLARE @Trans_Num numeric

set nocount on

--SET @strDatos='T23C02001000000000000000000020025047'

SET @ID= SUBSTRING(@strDatos , 10, 20 )
set @Monto= cast(SUBSTRING(@strDatos , 30, 7 ) as decimal(9,2))/100
set @TipMov= cast(SUBSTRING(@strDatos , 7, 3 ) as int)
set @Tienda= cast(SUBSTRING(@strDatos , 2, 2 ) as int)
set @NoCaja= cast(SUBSTRING(@strDatos , 5, 2 ) as int)
set @Fecha = convert(char(11), getdate(), 101)
set @Hora= CONVERT(VARCHAR,getdate(),108)
set @NoAuto=ABS(CHECKSUM(NewID())) % 100000 + 1

--Se guarda el monto Actual y el nombre en variables
SElect @SaldoActual = SALDO, @NomEmp = NOM_EMP, @Trans_Num=last_trans_num +1
FROM TBSALDOS
WHERE ID=@ID
--select @SaldoActual,@ID,@Monto,@TipMov,@Tienda, @NoCaja, @Fecha, @Hora,@NoAuto

if (@Monto<=@SaldoActual)
begin
set @SaldoNuevo=@SaldoActual-@Monto

BEGIN TRAN
insert TBMOVIMIENTOS
values(@trans_num,@ID,@TipMov,GETDATE(),@hora,@Tienda,@NoCaja,@SaldoActual,@Monto,@NoAuto)

IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRAN
GOTO ERR_HANDLER
END

Update TBSALDOS
SET SALDO=@SaldoNuevo, SALDO_ULT_MOV=@Monto,
DT_ULT_MOV=GETDATE(), HR_ULT_MOV=@Hora,
NO_TIE_ULT_MOV=@Tienda, NO_CAJA_ULT_MOV=@NoCaja,
last_trans_num=@Trans_Num
WHERE ID=@ID

IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRAN
GOTO ERR_HANDLER
END

SELECT 'OK'+ SUBSTRING(@strDatos , 1, 6 )+@id
+RIGHT(REPLICATE('0',7) + CAST(cast(@SaldoNuevo*100 as int) as varchar(10)) ,7)
+ RIGHT(REPLICATE(' ',40) +@NomEmp ,40)
+ + RIGHT(REPLICATE('0',6) + CAST(cast(@NoAuto as int) as varchar(10)) ,6) as resp

COMMIT TRAN

end
else
begin
ERR_HANDLER:
SELECT 'NO'+ SUBSTRING(@strDatos , 1, 6 )+@id
+RIGHT(REPLICATE('0',7) + CAST(cast(@SaldoActual*100 as int) as varchar(10)) ,7)
+ RIGHT(REPLICATE(' ',40) +@NomEmp ,40)
+ '000000' as resp
end
GO

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-13 : 17:33:00

So if you encounter an error, you want this to run:

SELECT 'NO'+ SUBSTRING(@strDatos , 1, 6 )+@id
+RIGHT(REPLICATE('0',7) + CAST(cast(@SaldoActual*100 as int) as varchar(10)) ,7)
+ RIGHT(REPLICATE(' ',40) +@NomEmp ,40)
+ '000000' as resp

If so, then you'll need to add a RETURN statement after you commit the data so that it doesn't get to that code on success. Right now if it runs with success, it'll commit the data, then make it into the ERR_HANDLER section as you haven't exited the stored procedure. To exit, use RETURN.

Tara Kizer
aka tduggan

shifis
Posting Yak Master

157 Posts

Posted - 2006-03-13 : 18:58:53

The return statement doesn't mean that I will receive the return value in my VB application.
If I put:

....
SELECT 'OK'+ SUBSTRING(@strDatos , 1, 6 )+@id
+RIGHT(REPLICATE('0',7) + CAST(cast(@SaldoNuevo*100 as int) as varchar(10)) ,7)
+ RIGHT(REPLICATE(' ',40) +@NomEmp ,40)
+ + RIGHT(REPLICATE('0',6) + CAST(cast(@NoAuto as int) as varchar(10)) ,6) as resp

COMMIT TRAN

return (0)
...

in my VB aplication

'Llamar el sp

'-------------------------------------

Set adors = New ADODB.Recordset
sSQL = "SP_SALDO '" & StrLine & "'"

adors.Open sSQL, gcnSQL, adOpenForwardOnly, adLockReadOnly, adCmdText

With adors

If adors.EOF And adors.BOF Then 'No encontro

Else
strResp = adors!resp

End If
adors.Close
End With
Set adors = Nothing
'-------------------------------------

I will get something like this:

OKT32C40000000000000000000020573597ELSA LOPEZ PRADO 084031

instead of 0 right?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-13 : 19:00:58

Don't RETURN a value as you aren't using it. Just put RETURN after the COMMIT TRAN.

COMMIT TRAN
RETURN

The RETURN statement is to exit the stored procecure without further processing. You are returning a result set to your application. That won't be impacted.

Tara Kizer
aka tduggan

shifis
Posting Yak Master

157 Posts

Posted - 2006-03-13 : 19:07:09

Thanks Tara!
I was confuse about the return part.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-14 : 01:25:26

Also read this to know more about error handling
http://www.sommarskog.se/error-handling-I.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources