Question Why Doesn't RAISERROR raise an error

[Matt]

I am trying to put some error handling into a couple of sprocs by using RAISERROR, that will be throw in my VB.NET application

I am starting simple:

CREATE PROCEDURE sp_test_transaction
AS

SELECT * FROM Saturn..gn_Colours


RAISERROR ('Error in deleting department in DeleteDepartment.', 16, 1)
RETURN


GO​

But this doesn't actually throw an error in my application. If I remove the select line an error is thrown as I want. Surely it should throw an error whether there is one or not if a RAISERROR is encountered?

 

[rcombs4]

I havent had any experience with RAISERROR but I would imagine it is not getting thrown because the sproc is returning the result of your sql query and not executing the rest of the sproc. You might want to add some flow control.

if <ERROR CASE> 
RAISERROR ('Error in deleting department in DeleteDepartment.', 16, 1)
Else
SELECT * FROM Saturn..gn_Colours

 

[Matt]

This seems to be the case but I just expected that the RAISERROR would raise an error regardless of whether there is actually an error internally in the sproc. If I run the sproc through the management studio it throws the error:

(295 row(s) affected)
[COLOR="Red"]Msg 50000, Level 16, State 1, Procedure sp_test_transaction, Line 8
Error in deleting department in DeleteDepartment.[/COLOR]

 

[rcombs4]

This is where my knowledge on the subject ends but I did make and execute basically the same sproc and found that Management Studio returns three different sets of data.

Under the results tab you see the result of the select query. this is what your asp.net code will see. Also under the results tab we see a scalar return value of -6 as defined in the RAISERROR function. I'm assuming if you could get this in your as.net code if you used .ExecuteScalar. And finally on the Messages tab you see the error that you posted above. I wouldnt know where to get this in asp.net code.

 

[pelegk2]

i think that RAISERROR dosnet beahvae the same way

i think that RAISERROR dosnet beahvae the same way
that when sql server throws an exception
read what i wrote here
vbCity/DevCity.NET Forums :: Visual Basic 6 :: VB6 General :: Sql server 2005/2008 try catch & raiserror
but i still didnt find a solution

 

[pelegk2]

i think that RAISERROR dosnet beahvae the same way

i think that RAISERROR dosnet beahvae the same way as the sql server itself
raises an error.
i wrote about it here too :
[ame=http://www.vbforums.com/showthread.php?p=3573126#post3573126]Sql server 2005/2008 try catch & raiserror - VBForums[/ame]
i dont think that his is an issue of the language (vb6/vb.net/c#...)
but rather there is a diffrence in the exeption thrown.
if you will look at the profiler you will that in the profier where there is an error you see it as an exception, but when you raiserror you see it like an error (although both of them with the same sevirity level)