Error handling in stored procedure

Question

Post reply

Error handling in stored procedure

Barry Anderberg

SSC Journeyman

Points: 99
More actions
June 21, 2004 at 1:20 pm

#86012

I have a stored procedure that looks like this:
CREATE PROCEDURE [insert_TestTable3_1]
(@Crap_1 [varchar](5000))
AS INSERT INTO [barry].[dbo].[TestTable3]
( [Crap])

VALUES
( @Crap_1)
IF @@ERROR <> 0
return @@ERROR
GO

I deliberately filled up my 2MB fixed size database so now if I call this stored procedure from the Query Analyzer I get this message:
Server: Msg 1105, Level 17, State 2, Procedure insert_TestTable3_1, Line 4
Could not allocate space for object 'TestTable3' in database 'barry' because the 'PRIMARY' filegroup is full.

I want some way to handle that error inside the stored procedure. It appears as though the stored procedure bails out if it can't complete the insert, but I want it to continue on and simply set the @@ERROR value so I can take appropriate action.

How can I do this?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

You can't.

Some errors aren't trappable from within a proc - they must be handled at the calling (client) side.

/Kenneth

AndyStyles SSC Veteran Points: 233 More actions · Answer 2

When you look at the @@Error variable, it it is reset to 0 afterwards.

So, to return it as a return from the stored procedure you would need to extract it into a variable. I have amended your code sample as follows:

CREATE PROCEDURE [insert_TestTable3_1]

(@Crap_1 [varchar](5000))

AS

Declare @errorcode As Int

INSERT INTO [barry].[dbo].[TestTable3]

( [Crap])

VALUES

( @Crap_1)

Select @errorcode = @@Error

IF @ErrorCode <> 0

return @errorcode

GO

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 3

The thing is, if the error generated from the INSERT statement is a batchbreaking error, the proc exits immediately, and you will never come to the 'Select @errorcode = @@Error' line.

Currently there is no way to trap all errors within a procedure itself.

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Isn't it strange, Kenneth? Each time I see a posting from you I can add a link to Erland Sommarskog

http://www.sommarskog.se/error-handling-II.html

http://www.sommarskog.se/error-handling-I.html

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 5

Isn't that nice?

I provide you loads of opportunities to post something.

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Yes, that's nice. Not much posting here anyway these days! Easy points

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 7

It's pretty calm over at sswug as well...

The sun must be shining somewhere

/Kenneth

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Well,... not here

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ron McCullough SSC Guru Points: 63877 More actions · Answer 9

Frank,

Just out of curosity I went to your links for error handling. All I can say is post these links often, it was a terrific read and realy, truly informative.

Thanks for tipping me off, so that I can better handle errors and not have to post to the forum to solve a problem that I handled by reading your post.

Again thanks

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

r_monov SSC Journeyman Points: 91 More actions · Answer 10

You cannot catch that error from inside the SP because its execution will never reach IF @@ERROR ...

But you can catch that error from the calling stored procedure or connection.

DECLARE @rc int

EXEC @rc=insert_TestTable3_1 'soso'

select @@ERROR -- put it first

SELECT @rc

The result is:

208

NULL

208 is the error code you need.

FuzzyDisk Newbie Points: 4 More actions · Answer 11

Thank you Frank for posting the Sommarskog links. 20 minutes of Google-ing yielded nothing as informative or on target.

Regards,

John