get RAISERROR from SQL Stored Procedure

basesuck · Mar 3, 2003

Hi,

how do I get the return value and the raiserror message text from a
sql stored procedure?

i have the following stored procedure: (dont worry about the german
variable names)

CREATE PROCEDURE [dbo].[usr_Get_Mitarbeitertyp](
@Mitarbeitertyp_ID UNIQUEIDENTIFIER = NULL
)
AS

DECLARE @Exist INT
DECLARE @Fehlertext NVARCHAR(250)

-- return only the specified dataset
IF @Mitarbeitertyp_ID <> NULL
BEGIN
SET @Exist = (SELECT COUNT (*)
FROM [dbo].[tbl_Mitarbeitertyp]
WHERE UID_Mitarbeitertyp_ID = @Mitarbeitertyp_ID)

-- dataset exists
IF @Exist = 1
BEGIN
SELECT tbl_Mitarbeitertyp.UID_Mitarbeitertyp_ID AS MitarbeitertypID,
tbl_Mitarbeitertyp.NVC_Mitarbeitertyp AS Mitarbeitertyp
FROM [dbo].[tbl_Mitarbeitertyp]
WHERE UID_Mitarbeitertyp_ID = @Mitarbeitertyp_ID
RETURN 1
END

-- dataset does not exist
ELSE
BEGIN
EXEC @Fehlertext = usr_Get_Fehler 3
RAISERROR (@Fehlertext,16,1)
RETURN 3
END
END

ELSE
BEGIN
SELECT tbl_Mitarbeitertyp.UID_Mitarbeitertyp_ID AS MitarbeitertypID,
tbl_Mitarbeitertyp.NVC_Mitarbeitertyp AS Mitarbeitertyp
FROM [dbo].[tbl_Mitarbeitertyp]
RETURN 1
END

GO

i also have a stored procedure that gets the error message from a
seperate table which is very simple:

CREATE PROCEDURE [dbo].[usr_Get_Fehler] (
@Fehler INT
)
AS

SELECT NVC_Fehlertext FROM tbl_Fehler WHERE I_Fehler_ID = @Fehler
RETURN 1
GO

when i test it in query analyser all works fine but i have hoped to
get the message as exception in my asp.net web application.
as you can see i used severity of 16 what seems to be a correct value.
(many posts before have this as main error cause)

all worked fine, updating worked fine, deleting and inserting worked
fine. only if an error occured i want to handle it as i handle it in
my stored procedure.
my code in asp looks like this: (in a click event)

Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles cmdSave.Click

Dim cmdUpdMitarbeitertyp As SqlClient.SqlCommand = _
New SqlClient.SqlCommand("usr_InsertUpdate_Mitarbeitertyp",
SqlConnection1)
cmdUpdMitarbeitertyp.CommandType = CommandType.StoredProcedure
SqlConnection1.Open() -> initialised in pageload event

Dim paramMitarbeitertyp As New
SqlClient.SqlParameter("@Mitarbeitertyp", _
SqlDbType.VarChar)
paramMitarbeitertyp.Direction = ParameterDirection.Input
paramMitarbeitertyp.Value = txtMitarbeitertyp.Text
cmdUpdMitarbeitertyp.Parameters.Add(paramMitarbeitertyp)

Dim strMitarbeitertypID As String
strMitarbeitertypID = Request.QueryString("id")

If Not strMitarbeitertypID = "" Then
Dim paramMitarbeitertypID As New
SqlClient.SqlParameter("@Mitarbeitertyp_ID", _
SqlDbType.UniqueIdentifier)
paramMitarbeitertypID.Direction = ParameterDirection.Input
paramMitarbeitertypID.Value = strMitarbeitertypID
paramMitarbeitertypID.Value =
System.Data.SqlTypes.SqlGuid.Parse(strMitarbeitertypID)
cmdUpdMitarbeitertyp.Parameters.Add(paramMitarbeitertypID)
End If

If Page.IsValid Then
Try
cmdUpdMitarbeitertyp.ExecuteNonQuery()
Catch Exp As SqlClient.SqlException
Label2.Text = Exp.Message & " ( " & Exp.Number & " )"
Finally
SqlConnection1.Close()
End Try
End If

End Sub

but i only get zero as message and 50000 as number, doesnt matter what
kind of error.
i have expencted that 3 is my exception number and the message from my
error table is in the message property of exception object
i also have tried to use the sqlerror-objekt and loop over it but
nothing.

maby i am in the wrong direction but my goal is to get the error
message that is trown by RAISERROR in stored procedure in asp.net when
the error occured. if not possible or something........ ;-)
it also would help me to get only the return value which is also the
id in my error table so that I fire another stored procedure against
it to get the message.

HELP !!!!!!!!!!!! and thanks in advance.

Andy

Nerseus · Mar 3, 2003

Not sure how youd get the return value "RETURN 1...", but you may not need it. I usually use the state parameter of RAISERROR to indicate any special value (you used 1 in your sample). You can get the values you need from the SqlException object. The number will always be 50000 unless you define your own error and raise it instead (with RAISERROR (50001, ...)).

Heres a sample:

Code:

// Assume the RAISERROR looks like:
RAISERROR (Hello World, 16, 1)
// The code looks like:
catch(SqlException e2)
{
	Debug.WriteLine(e2.Number); // Shows 50000
	Debug.WriteLine(e2.Message); // Shows "Hello World"
	Debug.WriteLine(e2.Class); // Shows 16
	Debug.WriteLine(e2.State); // Shows 1
}


// Assume the RAISERROR looks like:
RAISERROR (Hello World, string=%s, number=%d, 18, 21, bob, 42)
// The code looks like:
catch(SqlException e2)
{
	Debug.WriteLine(e2.Number); // Shows 50000
	Debug.WriteLine(e2.Message); // Shows "Hello World, string=bob, number=42"
	Debug.WriteLine(e2.Class); // Shows 18
	Debug.WriteLine(e2.State); // Shows 21
}

-Nerseus

basesuck · Mar 4, 2003

Hi Nerseus,

thanks for your fast help.

Thats what I tried but my message is still 0. The state, class and number are like above but as message I get 0.
I also tried not to use my error-text stored procedure and type a string (like your hello world) in my raiserror but still 0.
I thought that maby I have to enable something in VS oder in SQL Server but I don

Nerseus · Mar 4, 2003

Ah, I didnt look close enough at your usr_Get_Fehler proc. You cant assign a string like you are with:
EXEC @Fehlertext = usr_Get_Fehler 3

Youll have to use an output variable, as in:

Code:

CREATE PROCEDURE [dbo].[usr_Get_Fehler] (
@Fehler INT,
@NVC_Fehlertext NVARCHAR(250) output
) AS
SELECT @NVC_Fehlertext = NVC_Fehlertext FROM tbl_Fehler WHERE I_Fehler_ID = @Fehler



--From within a proc, use the following:
-- dataset does not exist

ELSE
    BEGIN
        EXEC usr_Get_Fehler 3, @Fehlertext output
        RAISERROR (@Fehlertext,16,1)
        RETURN 3
    END
END

-Nerseus

basesuck · Mar 5, 2003

Hi Nerseus

thanks a lot..............
I mixed all tips and found a good solution.
In all my stored procedures I use the RETURN statement with a number and get this number with the ParameterDirection Return Value
This number is also the id in my error table.
I wrote a class that has one method which gets the error text from my error table assosiated with the id
In my error stored procedure I have defines an Output for the
ErrorText. Thats all...

Just as information for everyone whos interested here the code for my class (the thin version without error handling):

Public Class Fehler

Public Function getErrorMessage(ByVal intError As Integer, ByVal conn As SqlClient.SqlConnection)

Dim daError As SqlClient.SqlDataAdapter = _
New SqlClient.SqlDataAdapter("usr_get_fehler", conn)
Dim dsError As New DataSet()
daError.SelectCommand.CommandType =
CommandType.StoredProcedure

Input-Parameter
daError.SelectCommand.Parameters.Add _
(New SqlClient.SqlParameter("@Fehler", SqlDbType.Int))
daError.SelectCommand.Parameters("@Fehler").Direction =
ParameterDirection.Input
daError.SelectCommand.Parameters("@Fehler").Value = intError

Return Value
daError.SelectCommand.Parameters.Add _
(New SqlClient.SqlParameter("RetVal", SqlDbType.Int))
daError.SelectCommand.Parameters("RetVal").Direction =
ParameterDirection.ReturnValue

Output-Parameter
daError.SelectCommand.Parameters.Add _
(New SqlClient.SqlParameter("@FehlerOut", SqlDbType.NVarChar,
250))
daError.SelectCommand.Parameters("@FehlerOut").Direction =
ParameterDirection.Output

daError.Fill(dsError, "usr_get_fehler")

getErrorMessage =
daError.SelectCommand.Parameters("@FehlerOut").Value

daError.Dispose()
conn.Close()

End Function

So I only have to instanziate the object and call the method like:

dim fehler as new Fehler()
fehler.getErrorMessage (intErrNum, SQLConn)

Nerseus · Mar 5, 2003

Sounds like a good candidate for a static method to me (unless you plan on calling more than on method on your Fehler class).

-Nerseus

get RAISERROR from SQL Stored Procedure

basesuck

New member

Nerseus

Danner

basesuck

New member

Nerseus

Danner

basesuck

New member

Nerseus

Danner