Showing posts with label exception. Show all posts
Showing posts with label exception. Show all posts

Friday, March 30, 2012

Raiserror

Is there a way to raise an exception inside a user function in the Sqlserver2000?.Guess not

Did you check out BOL for specifics?

CREATE FUNCTION udf_myFunction99 (@.x varchar(8000))
RETURNS int
AS
BEGIN
RAISERROR 50001 'Error Raised'
RETURN -1
END
GO|||Originally posted by Brett Kaiser
Guess not

Did you check out BOL for specifics?

CREATE FUNCTION udf_myFunction99 (@.x varchar(8000))
RETURNS int
AS
BEGIN
RAISERROR 50001 'Error Raised'
RETURN -1
END
GO



I got the error

Server: Msg 443, Level 16, State 2, Procedure udf_myFunction99, Line 5
Invalid use of 'RAISEERROR' within a function.|||Yeah, I know...That's why I said:

"guess not"

Check out books online for a better description of Functions...

I know there a few limitations...

What are you trying to do?|||Originally posted by Brett Kaiser
Yeah, I know...That's why I said:

"guess not"

Check out books online for a better description of Functions...

I know there a few limitations...

What are you trying to do?

There are many limitations I know, but this one I didn't found.
I supose its not allowed to use RAISERROR in functions.
I am generating a calc module from a CASE system using only functions. So far I found a solution for every limitation. Otherwise I will have to rebuild the entire system using only stored procedure instead of functions.

That1s why I am looking for some "magic" to raise the exception.

Thank You.|||...or we could hope for a miracle...

Since you haven't been able to raise out in the other functions, why do you want to with this one?

And yeah, sprocs would have been the way to go (MOO)...how are you using the functions?

BOL

The following statements are allowed in the body of a multi-statement function. Statements not in this list are not allowed in the body of a function:

Assignment statements.

Control-of-Flow statements.

DECLARE statements defining data variables and cursors that are local to the function.

SELECT statements containing select lists with expressions that assign values to variables that are local to the function.

Cursor operations referencing local cursors that are declared, opened, closed, and deallocated in the function. Only FETCH statements that assign values to local variables using the INTO clause are allowed; FETCH statements that return data to the client are not allowed.

INSERT, UPDATE, and DELETE statements modifying table variables local to the function.

EXECUTE statements calling an extended stored procedures.sql

raiseerror does not raise exception

Hi friends

i've a stored proc (sql 2005) that'll raiseerror statement when something violated.

but my C# application that calls this stored proc does not any throw exception when this happens !!

i remember visual basic used to through an exception for this type of things.

is it different in C# and how do we handle this scenario ?

Thanks for ur ideas.

can you show us the SP code?

moving thread to the SQL Forums

|||am doing something like below

BEGIN TRY
BEGIN TRAN

/* one of below statements may result in error*/
insert into mytable1 values (...blah..)
insert into mytable1 values (...blah..)

COMMIT
END TRY
BEGIN CATCH
DECLARE

@.ErrorMessage VARCHAR(4000)
SELECT @.ErrorMessage = 'Message: '+ ERROR_MESSAGE();

raiserror (@.ErrorMessage)
END CATCH;

i used executescalar ,executereader (in C#) but none of them throw any exception but i could access data CATCH returns though|||

Hi prk,

You'll need to specify the severity and state during the RAISERROR call:

raiserror (@.ErrorMessage,16,1)

Cheers,

Rob

|||

Thanks Rob

will give that a try