Error Handling in Sql Server 2000

I am having a SP (Stored Procedure) I want to handle the error in any case for the SP. My SP is inserting a record in a table when I had changed the Column name of the Table the error is not traced but the error is directly displayed in the TSql. How to Handle any error that occured in the SP? Thanks in Advance.

CREATE PROCEDURE SP_InvoiceFromRBOPInvNoHdr @.AutoBillID int, @.NextBillDate DateTime, @.OPInvNo int OutPut
AS
declare @.InvNo int
declare @.CustID int
declare @.InvAdd1 varchar(50)
declare @.InvAdd2 varchar(50)
declare @.city varchar(50)
declare @.Postal varchar(20)
declare @.Country varchar(50)
declare @.Province varchar(50)
declare @.BillDate datetime
declare @.BillingContact varchar(50)
declare @.BillingCurrencyCode varchar(5)
declare @.StoreID int
declare @.BillingPeriod varchar(10)
declare @.BillingTime varchar(10)
declare @.comment varchar(100)
declare @.TotalBillAmount float
declare @.TotalTax1 float
declare @.TotalTax2 float

declare @.err int

if exists(select * from BillRecurringInvoiceHeader where AutoBillID=@.AutoBillID)
begin
select @.InvNo = IsNull(max(InvoiceNumber),0)+1 from BillInvoiceHeaderHistory

select @.CustID=CustID,@.InvAdd1 = Address1, @.InvAdd2 = Address2, @.city=City,@.Postal=Postal,@.Country=Country,@.Province=Province,
@.BillDate = NextBillDate,@.BillingContact=BillContactName,@.BillingCurrencyCode=BillCurrencyCode,@.StoreID=StoreID,@.BillingPeriod=BillingPeriod,@.BillingTime=PreferredBillingTime
from BillRecurringInvoiceHeader where AutoBillID=@.AutoBillID

Set @.comment = 'Invoice from BillID ' + Cast(@.AutoBillID as varchar(10))
exec SP_RBAmount @.StoreID, @.AutoBillID, 'I',@.TotalBillAmount Output, @.TotalTax1 output, @.TotalTax2 output
--BillInvoiceHeaderHistory
insert into BillInvoiceHeaderHistory (InvoiceNumber,BillingAddress1,BillingAddress2,City,
Province,Postal,Country,BillingContact,BillDate,Comments,TotalBillAmount,TotalTax1,TotalTax2,BillingCurrencyCode,
AutoBillID,TotalRcdAmount,Status,StoreID,CustID) values (@.InvNo,@.InvAdd1,@.InvAdd2,@.city,
@.Province,@.Postal,@.Country,@.BillingContact,@.BillDate,@.comment, @.TotalBillAmount, @.TotalTax1, @.TotalTax2,
@.BillingCurrencyCode,@.AutoBillID,0,Null,@.StoreID,@.CustID)

select @.err = @.@.ERROR
if @.err != 0
begin
--Error Occured
--Set @.OPInvNo = -1
return @.err
end
else
begin
--No Errors
Set @.OPInvNo = @.InvNo
end
end
GO

Error handling in sqlserver2000 is really a pain, you have to check the @.@.ERROR after any statement that may fail.

declare @.err int

select @.InvNo = IsNull(max(InvoiceNumber),0)+1 from BillInvoiceHeaderHistory

set @.err = @.@.error

if (@.err != 0) begin ... end

select @.CustID=CustID,@.InvAdd1 = Address1, @.InvAdd2 = Address2, @.city=City,@.Postal=Postal,@.Country=Country,@.Province=Province,

@.BillDate

=

NextBillDate,@.BillingContact=BillContactName,@.BillingCurrencyCode=BillCurrencyCode,@.StoreID=StoreID,@.BillingPeriod=BillingPeriod,@.BillingTime=PreferredBillingTime

from BillRecurringInvoiceHeader where AutoBillID=@.AutoBillID

set @.err = @.@.error

if (@.err != 0) begin ... end

Set @.comment = 'Invoice from BillID ' + Cast(@.AutoBillID as varchar(10))

set @.err = @.@.error

if (@.err != 0) begin ... end

exec SP_RBAmount @.StoreID, @.AutoBillID, 'I',@.TotalBillAmount Output, @.TotalTax1 output, @.TotalTax2 output

set @.err = @.@.error

if (@.err != 0) begin ... end

--BillInvoiceHeaderHistory

insert into BillInvoiceHeaderHistory (InvoiceNumber,BillingAddress1,BillingAddress2,City,

Province,Postal,Country,BillingContact,BillDate,Comments,TotalBillAmount,TotalTax1,TotalTax2,BillingCurrencyCode,

AutoBillID,TotalRcdAmount,Status,StoreID,CustID) values (@.InvNo,@.InvAdd1,@.InvAdd2,@.city,

@.Province,@.Postal,@.Country,@.BillingContact,@.BillDate,@.comment, @.TotalBillAmount, @.TotalTax1, @.TotalTax2,

@.BillingCurrencyCode,@.AutoBillID,0,Null,@.StoreID,@.CustID)

set @.err = @.@.error

if (@.err != 0) begin ... end

|||

I had changed the column name of the table and this error is what I am unable to handle. The SP stops in the insert statement only. I had changed the column BillingAddress2 to BillingAddress23 and I am getting this error :

Server: Msg 207, Level 16, State 1, Procedure SP_InvoiceFromRBOPInvNoHdr, Line 39
Invalid column name 'BillingAddress2'.

|||

Check this post..

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=944372&SiteID=1

|||

Just to add my 2 cents worth, Error Handling in SQL Server 2000 was pretty weak. Check this article: http://www.sommarskog.se/error-handling-I.html for a good bit of information about this, and this one that is about stored procedures: http://www.sommarskog.se/error-handling-II.html. Bottom line, in 2000 every error was sent to the client. In 2005, you can take care of some of this with TRY..CATCH, but not all.

I don't think even 2005 will help you one a column name change, this is something that you need to manage in your code/testing.

|||

There is no way in 2000 or 2005 to trap an errror if the column does not exist. The error trapping in 2000 and 2005 is almost non-existent.

You cannot trap a non-existent column in either version.

|||

I wouldn't put it that way: "The error trapping in 2000 and 2005 is almost non-existent"

There are only a few errors that cannot be trapped, and I am not even sure if it would be a good idea for this kind of error to be trapped. This is a compilation error, so more or less the code cannot be executed. SQL is a very flexible language (exec ("select ...") for example) but it shouldn't be that flexible. It does catch most errors that it should and is 1000 times better than in 2000.