RaisError does not raise error with Enterprise Lib

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

RaisError does not raise error with Enterprise Lib

Author

Topic

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2008-05-19 : 10:26:20

Hi all,

I'm using SQL Management Studio 2005 to work on a SQL Server 2000. I have a problem with using RAISERROR in a stored procedure. I've read this article but could not find the problem.[url]http://www.sqlteam.com/article/handling-errors-in-stored-procedures[/url].

I've created this SP:

ALTER PROCEDURE [dbo].[RaisErrorTest] 
AS
BEGIN
	RAISERROR ('An error occured updating the NonFatal table',10,1)
END

Then I call the SP

DECLARE	@return_value int
EXEC	@return_value = [dbo].[RaisErrorTest]
SELECT	'Return Value' = @return_value

Now it displays 2 tabs: On the Results tab there is a column 'Return Value' which has a single row displaying a zero. On the Messages tab I got this message:
An error occured updating the NonFatal table

(1 row(s) affected)

When I remove the SELECT-Statement from the call I only get the message. But unfortunately I use Microsofts Enterprise Library [url]http://msdn.microsoft.com/en-us/library/cc339164.aspx[/url] , and it seems that this one automatically adds this kind of final SELECT-Statement. So I call UpdateDataSet and it ALWAYS returns a valid code, regardless whether RAISERROR is called or not.

I guess I should ask at an Enterprise Library board, but maybe someone here already had this problem?
Thanks!

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-19 : 12:30:30

try this

alter PROCEDURE [dbo].[RaisErrorTest]
AS
begin
RAISERROR ('An error occured updating the NonFatal table',10,1)
return 10
end

An infinite universe is the ultimate cartesian product.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-19 : 14:14:30

You can get more advanced like jumping to an ErrorHandler and such, but here is a basic one:

ALTER PROCEDURE [dbo].[RaisErrorTest] 
AS
BEGIN
	RAISERROR ('An error occured updating the NonFatal table',10,1)
	RETURN @@ERROR
END

-- OR

ALTER PROCEDURE [dbo].[RaisErrorTest] 
AS
BEGIN
	DECLARE @Error INT

	RAISERROR ('An error occured updating the NonFatal table',10,1)
	SET @Error = @@ERROR

	RETURN @Error
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-19 : 14:26:58

RAISERROR 10 is nothing more than RAISERROR 0, a PRINT statement.
Books Online clearly states that using severity level 10 is just for backwards compatibility.

E 12°55'05.25"
N 56°04'39.16"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-19 : 14:51:56

Ahh, yes. I didn't notice the severity of 10. Good call Peso. :)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-19 : 15:26:59

Thanks.

OP might use severity level 16 for user errors (such as mismatched input) and severity level 18 for other errors.

E 12°55'05.25"
N 56°04'39.16"

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2008-05-19 : 16:15:03

quote:

Originally posted by Lamprey

You can get more advanced like jumping to an ErrorHandler and such, but here is a basic one:

ALTER PROCEDURE [dbo].[RaisErrorTest] 
AS
BEGIN
	RAISERROR ('An error occured updating the NonFatal table',10,1)
	RETURN @@ERROR
END

-- OR

ALTER PROCEDURE [dbo].[RaisErrorTest] 
AS
BEGIN
	DECLARE @Error INT

	RAISERROR ('An error occured updating the NonFatal table',10,1)
	SET @Error = @@ERROR

	RETURN @Error
END

Yeah sure OR


ALTER PROCEDURE [dbo].[RaisErrorTest] 
AS
BEGIN
	DECLARE @Error INT
        Set @Error = 10

	RAISERROR ('An Error occured because I said so',@ERROR,1)

	RETURN @Error
END

OR if you really want to get fancy.


ALTER PROCEDURE [dbo].[RaisErrorTest] 
AS
BEGIN
	DECLARE @Error INT
	DECLARE @Error2 INT
	DECLARE @Error3 INT
        
select @Error2 = Case when @Error = 10 then @error else 10 end


	RAISERROR ('A programmer is being silly',@ERROR2,1)
        select @error3 = @@ERROR
	RETURN @Error3
END

An infinite universe is the ultimate cartesian product.

Heinz23
Yak Posting Veteran

84 Posts

Posted - 2008-05-20 : 03:23:22

Works fine, thanks to you all!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources