RaisError in a function? Any ideas

Writing a function and need to raise an error under certain conditions. This is a gross over-simplification of the function. But you get the idea....I need to use @.@.ROWCOUNT

Code Snippet

create Function GetTheValue

(@.Input INT)

returns Bigint

as

begin

declare @.Output bigint

select TheValue

from TheTable

where RowID = @.Input

IF @.@.ROWCOUNT = 0

RAISERROR('Oops',15,1)

return @.INput

END

Basically, the way this usually works is that you return an invalid value and check for it. Like in this case, you can never get a negative value for @.@.rowcount, just return -1 or something like this.

You can force an error to occur by doing something like 0/1, but I was never fond of that method since the error message doesn't correspond to the actual error.

|||

As you noticed, RAISERROR is NOT allowed in a FUNCTION.

A FUNCTION cannot have any output EXCEPT the FUNCTION value. Of course, it will 'crash' when unacceptable actions occur, such as divide by zero.

Louis indicated an acceptable method of handling a data anomaly.

|||

Thanks....

I'll probably use:

Code Snippet

if @.@.rowcount =0

select 1/0

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

Quick question: how do I get the name of the current database?

Is there an sp_zzzzzz function to return the name of the current database?
I would like to use this name as a variable in a stored procedure in order
to create names for further databases (by appending a tag, such as
MYDATABASE_BLOB001, ..._BLOB002 etc.

Thanks.SELECT DB_NAME()

Why create a database from a Stored Procedure?
--
David Portas
SQL Server MVP
--|||"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:ct5isn$9m3$1$8302bc10@.news.demon.co.uk...
> Is there an sp_zzzzzz function to return the name of the current database?
> I would like to use this name as a variable in a stored procedure in order
> to create names for further databases (by appending a tag, such as
> MYDATABASE_BLOB001, ..._BLOB002 etc.
>
> Thanks.

select db_name()

It might be worth considering why you need multiple databases - could
BLOB001 be part of a key in a table instead? This discussion applies to
tables, not databases, but the principle is exactly the same:

http://www.sommarskog.se/dynamic_sql.html#Sales_yymm

Simon|||The issue is that we have one database with our adjacency list in and then
one or more with our media blobs in. Our users won't be using full SQL
server, so we have 2Gb limit on MSDE. When one of the blob databases
reaches close to 2Gb, we rollover to the next blob database. In my
adjacency table I store the blob database ID and Key for the blob associated
with each node. I also have a table with all of the blob databases listed,
so I can lookup and find which database the ID refers to.

Can't help it. Customers are cheapskates ;)

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41f65413$1_3@.news.bluewin.ch...
> "Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
> message news:ct5isn$9m3$1$8302bc10@.news.demon.co.uk...
>> Is there an sp_zzzzzz function to return the name of the current
>> database? I would like to use this name as a variable in a stored
>> procedure in order to create names for further databases (by appending a
>> tag, such as MYDATABASE_BLOB001, ..._BLOB002 etc.
>>
>>
>> Thanks.
>>
>>
>>
> select db_name()
> It might be worth considering why you need multiple databases - could
> BLOB001 be part of a key in a table instead? This discussion applies to
> tables, not databases, but the principle is exactly the same:
> http://www.sommarskog.se/dynamic_sql.html#Sales_yymm
> Simon|||"Robin Tucker" <idontwanttobespammedanymore@.reallyidont.com> wrote in
message news:ct5l4s$ig$1$8300dec7@.news.demon.co.uk...
> The issue is that we have one database with our adjacency list in and then
> one or more with our media blobs in. Our users won't be using full SQL
> server, so we have 2Gb limit on MSDE. When one of the blob databases
> reaches close to 2Gb, we rollover to the next blob database. In my
> adjacency table I store the blob database ID and Key for the blob
associated
> with each node. I also have a table with all of the blob databases
listed,
> so I can lookup and find which database the ID refers to.
> Can't help it. Customers are cheapskates ;)

Something to be aware of: I think that MSDE may also limit you on the number
of databases you are allowed so there may be a limit on the number of times
you can "rollover" to another database.

Brian.

www.cryer.co.uk/brian