April 29, 2008 at 4:44 am
I'm trying to write some code to create a table is a stored procedure (easy) and return the @@error in case the creation fails.
In principle it is very easy, something along the lines of :
create procedure my_proc ( @return_the_error int output) as
create table fred (col 1 int)
select @return_the_error=@@error
This can feed into calling procedures to direct actions according to whether the table already exists / privileges don't exist etc.
However, execution ceases with a standard error message when a problem occurs - in query analyser I can then do select @@error to find out the reason but in a stored procedure the routine has already halted.
Can anyone tell me how to stop execution halting untidily please so my code can continue - it might be something obvious I've just not spotted yet?
Thanks
April 29, 2008 at 5:59 am
did you try Try...Catch?
April 29, 2008 at 6:33 am
Not an option unfortunately as they are a feature added in 2005
April 29, 2008 at 4:15 pm
IF (@return_the_error > 0)
RETURN @return_the_error
Also, consider this when trying to create a table:
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.tables where table_name = 'Fred')
BEGIN
create table fred (col 1 int)
END
April 29, 2008 at 5:51 pm
For error handling, please be sure to read Erland Sommarskog's article at
http://www.sommarskog.se/error-handling-I.html
Based on the article and all of the conditions where error handling cannot be performed, I recommend having all the error handling in the client and almost no error handling in stored procedures. Note that error handling does not work in triggers and function. Stored procedure behavior can be made the same as triggers and function by including "set xact_abort on" (which is required for most distributed queries)
Notice that under both SQL Server 2005 and 2008, when there is an invalid object reference, error handling does not work:
create proc trycatch_test
as
set nocount on
BEGIN TRY
select * from ThisTableDoesNotExist
END TRY
BEGIN CATCH
print 'error caught'
END CATCH
;
go
The only error handling that should be included is checking the return code when executing a stored procedure.
Here is the error handling that I include:
-- Standard statements at the start of stored procedures
set nocount on
set xact_abort on
DECLARE @LocalError int
,@LocalTranCountint
set@LocalTranCount = @@trancount
-- executing a stored procedure error handling
EXEC @LocalError = some_other_sp
set @LocalError = coalesce(nullif(@LocalError, 0), @@error)
IF @LocalError <> 0
BEGIN
IF @LocalTranCount = 0 ROLLBACK TRANSACTION
RETURN @LocalError
END
SQL = Scarcely Qualifies as a Language
April 30, 2008 at 1:19 am
That gives me enough to make sensible forward progress - many thanks for taking the time to give me that help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply