How to get past an error?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have posted around this issue before, and am still looking for a soultion.

There are several situations where we have tsql code that could generate a
sql error but still need to return a recordset or print statement that
occours after the error.

I have been using the sqlclient class.

In the past (ado not ado.net) we could get errors followed by print
statemets and depending on the type of error, might see the recordset.. Now
with sqlclient the error blocks everything (even thought the code in the proc
completes with results.

It seems that the new envirionment is squashing the value of submitting code
in batchs.. As any error in a batch causes you to miss everthing else.

In QA I can see the errors, the print statements and the rows
(resultsets/recordsets/datatables - what ever you want to call them)

Does anyone know if this is going to be fixed (or are their any
workarounds).. Or do I need to programs all of my code to ODBC if I want to
see (and have my users) see all the results of their actions?

TIA

Rob
PS try to make this proc send back the error,print and get the record (row)
of databack if you are wondering what i am talking about.

Create proc someproc
as
declare @e int
delete from northwind.dbo.employees where employeeid = 2
--as far as ado.net is concerned, nothing happens after the above line :(
set @e = @@error
if @e <> 0
begin
print 'error number ' + cast (@e as varchar(300)) + ' Just happened'
end
select * from northwind.dbo.employees where employeeid = 1
 
What you need to do is code your stored procedures so that *all*
errors are handled and returned to your client code in output
parameters or result sets. Print statements only work in QA, so you
can't rely on them in client code. Bear in mind that unlike SqlClient
(or any other provider) in T-SQL code continues to execute even after
an error is returned, with @@error getting reset to 0 on the line
following the line where the error occured. You need to capture this
in local variables in your sproc so that this information can be
returned to your client code.

--Mary
 
Back
Top