How to NOT trap errors in a stored procedure

By Chris Miller on 1 December 2001 | Tags: Stored Procedures


I was trying to track down an error in one of our internal OLAP collection processes, when I noticed something very peculiar: An insert was failing, appeared to have the proper error trapping, but was not causing an error. How does that work?

Let's say you've got a batch that looks like this:


declare @err int,
@RC int


set @err = 0
set @rc = 0

insert into BigTable
select * from ImportTable

set @RC = @@ROWCOUNT
set @err = @@ERROR

if @@ERROR <> 0
begin
rollback transaction
print 'Transaction Failed'
end

So, what is the value of @err if the INSERT fails? It's the same as the value if the INSERT works. @@ERROR and @@ROWCOUNT are only valid for the statement which executed immediately previous to the use of the variable. So, the @@ERROR was quite unhelpfully trapping any errors which resulted from the assignment of @@ROWCOUNT to @RC. Not exactly as intended. The correct way of writing it is to use SELECT, like this:

select @RC = @@ROWCOUNT, @err = @@ERROR




-rs


Related Articles

Handling SQL Server Errors (5 April 2010)

Testing with Profiler Custom Events and Database Snapshots (22 June 2009)

Debugging Stored Procedures in Visual Studio 2005 (25 June 2007)

Writing CLR Stored Procedures in C# - Returning Data (Part 2) (23 June 2005)

Writing CLR Stored Procedures in C# - Introduction to C# (Part 1) (6 June 2005)

Managed Data Access Inside SQL Server with ADO.NET and SQLCLR (31 May 2005)

An Evaluation of Stored Procedures for the .NET Developer (22 March 2004)

Run CLR code from a stored procedure (14 October 2003)

Other Recent Forum Posts

|Danmark| Temu kuponkode [acp856709] 700kr rabat + 40% rabat For førstegangsbestilling (10m)

Temu Canada Black Friday Sale: 90% Off {acs540599} with Free Shipping 2024 (4h)

Temu Black Friday Deal: $200 Off {aci622722} with Free Shipping 2024 (5h)

Temu Black Friday Special: $200 Off {acx377627} with Free Shipping 2024 (5h)

New Temu Pakistan Coupon Code: {act530281} Rs. 15000 with Free Shipping 2024 (6h)

Special Temu Pakistan Coupon Code: {acs540599} 90% Off with Free Shipping (6h)

Error query return more than one value (7h)

Verbose truncation warnings setting not working (13h)

- Advertisement -