get RAISERROR from SQL Stored Procedure

[basesuck]

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]

Not sure how you'd 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, ...)).

 

Here's a sample:

// 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]

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´t have any idea where I have to.

It would be nice if you have some additional ideas. Thanks in advance.

Andy

[Nerseus]

Ah, I didn't look close enough at your usr_Get_Fehler proc. You can't assign a string like you are with:

EXEC @Fehlertext = usr_Get_Fehler 3

 

You'll have to use an output variable, as in:

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]

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]

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