• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6675
  • Last Modified:

Cannot use Raiserror in a UDF?

I get the following error when I attempt to raise an error in a scalar UDF, SQL Server 2005.

      Msg 443, Level 16, State 14, Procedure sample_of_the_problem, Line 6
      Invalid use of side-effecting or time-dependent operator in 'RAISERROR' within a function.

The documentation doesn't explicity say anything about raising an error. If anyone has a definitive answer about whether or not this is ever supported, let me know.

create function sample_of_the_problem(
      @ID integer
) returns integer
as begin
  if @ID is null begin
      raiserror('Invalid ID',16,1)
      return 0
  end
  return 1
end

Works fine if if the raiserror is commented-out.  Returns same error number with a slightly different message (Invalid use of 'RAISEERROR' within a function) in SQL Server 2000.

I tried to get tricky and call a stored procedure that raises the error, but that's not permitted either. Interestingly, this error occurs during the CALL to the function, not when the function is created.

    Msg 557, Level 16, State 2, Line 1
    Only functions and extended stored procedures can be executed from within a function.

So I'm left with using some kind of signaling value to indicate error conditions.

-- Craig
0
Craig Yellick
Asked:
Craig Yellick
  • 7
  • 5
  • 3
  • +3
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
UDF wont support RAISERROR
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
from 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
0
 
Craig YellickDatabase ArchitectAuthor Commented:
Thanks. I read the BOL very carefully. As I wrote: it does not say anything *explicitly* about raiserror, so I'm asking if anyone else knows if there is a way to raise an error condition from a UDF. Returning a special signal value is not desirable but appears to be my only option. Am hoping this is not the case.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Aneesh RetnakaranDatabase AdministratorCommented:
its specified in BOL that 'Statements not in this list are not allowed in the body of a function', and the  RAISERROR statement does not fall inside any of the statements specified there
0
 
Anthony PerkinsCommented:
>>As I wrote: it does not say anything *explicitly* about raiserror<<
It falls under the section deterministic or nondeterministic.  If it is deterministic you can use it other wise you cannot. Answer that question and you will have found the answer yo your question.
0
 
Anthony PerkinsCommented:
P.S.  No points for me, aneeshattingal had already answered your question.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
CraigYellick,
> Returning a special signal value is not desirable but appears to be my
> only option
I think you should go for either returning some error value or if possible convert this fn as an sp
0
 
Craig YellickDatabase ArchitectAuthor Commented:
Converting to a stored procedure is not an option, the usage involves an expression.

Folks, I completely and totally understand that the documentation says "here's what you can do", and that the raiserror statement isn't explicitly listed.

Raising an error condition could be considered a "Control-of-Flow statement". From the wording of the error message in SQL 2005, I was hoping the implication was that raiserror COULD be called if called correctly. I tried every syntax variation I could think of.

Raising an error does not seem to me to be non-deteministic. An error is an error and it is troublesome that there is no way to signal it.

For example, try this with the Northwind database:

    select convert(integer,postalcode) from customers where country='usa'

Remove the WHERE clause an run it again. Clearly, it's possible for a function to interrupt a SELECT statement while it is processing.

-- Craig
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
CraigYellick,
> select convert(integer,postalcode) from customers where country='usa'
 first the filter is applied then the conversion takes place, since there are no special charecters in postalcode for usa in this db, it wont give an error ? but when you remove it , there is no fileter so, it will give an eroro.
Now i don't understand how you can compare this with Raiserror ?
0
 
Craig YellickDatabase ArchitectAuthor Commented:
Aneeshattingal,

The convert function is a FUNCTION.

The convert function RAISES AN ERROR during processing.

Notice that the error exactly the same kind of error as any other error: it has a number, is in the sys.messages table etc.  The syntax for raising these kind of errors involves theraiserror statement.

Therefor, it is possible for functions to raise errors in SQL Server. I am asking if anyone knows if there is a way for a user function to return an error, since clearly SQL Server is capable of doing so.

Maybe there is a special form of syntax for raiserror, or perhaps an entirely different mechanism of which I am not aware. Maybe a system stored procedure, a system function, something. Anything.

-- Craig
0
 
mastooCommented:
I'll toss in an extremely ugly answer - you can force a divide by zero error in a dummy select statement to interrupt processing.
0
 
Scott PletcherSenior DBACommented:
Actually, there is a sneaky way to do it, as long as you don't need to pass variable data to the err msg.
0
 
Craig YellickDatabase ArchitectAuthor Commented:
Mastoo: interesting! As a last (yes ugly) resort, that might do it.

ScottPletcher: anything, anything! Any limitation is better than nothing.
0
 
Scott PletcherSenior DBACommented:
Make the server a "linked server" to itself:

EXEC sp_addLinkedServer 'serverName', N'SQL Server'
EXEC sp_addLinkedSrvLogin 'serverName', 'TRUE', NULL
    --or use 'FALSE' and assign a specific id to use for these requests
EXEC sp_serverOption 'serverName', 'data access', 'ON'


Then use OPENQUERY() to do the RAISERROR:

alter function sample_of_the_problem(
     @ID integer
) returns integer
as begin
  if @ID is null begin
     SELECT @id = @@error FROM OPENQUERY(serverName, 'RAISERROR(''Invalid ID, cannot be NULL.'', 16, 1) SELECT @@ERROR')
     return @id
  end
  return 1
end


SELECT dbo.sample_of_the_problem(NULL)
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
ScottPletcher,
> Then use OPENQUERY() to do the RAISERROR:

I dont think OPENQUERY will do this , I tried this yesterday its giving this error

Server: Msg 7357, Level 16, State 2, Line 1
Could not process object 'RAISERROR (50001,16,1)'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns.
OLE DB error trace [Non-interface error:  OLE DB provider unable to process object, since the object has no columnsProviderName='SQLOLEDB', Query=RAISERROR (50001,16,1)'].
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Sorry..I didn't saw svcott's post..
0
 
Craig YellickDatabase ArchitectAuthor Commented:
Thanks to everyone who participated.

The divide-by-zero technique is simple and effective, and no worse or more misleading than returning a special signal value from the function. I like it.

The OPENQUERY technique can raise a specific, distinct error but at the cost of requiring a self-linked server and the attendant security and configuration hassles. Extra points for creative problem solving!

It is absurd that there isn't a way to raise an error condition in a UDF. I'll be posting this as a wish list item to the SQL Server team!

-- Craig
0
 
mastooCommented:
Glad that helps.  We needed to retrofit an error on a bunch of old stuff that absolutely needed to stop under certain conditions.  Once a year, the error happens, we puzzle over the message "divide by zero", go look at the code and then remember - oh that's right it really means this...
0
 
Scott PletcherSenior DBACommented:
IMO that's a worse way to do it, since the error is incredibly misleading.  And we wonder why companies are outsourcing development to India ...
0
 
mrgilbe1Commented:
Found another ugly, ugly solution in another forum:

http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/2d1b22a4-0bdd-4446-bd8a-37f9f8e09cdc/

Basically, write your error as a string and then attempt to assign that string to an integer.  You get an error like this:

Msg 245, Level 16, State 1, Line 2
Conversion failed when converting the varchar value 'Your error message goes here.' to int.

Who's bright idea with in the SQL server team was it, to prevent the use of RAISERROR within functions?  They should offshore SQL Server dev to india, if they make mistakes like that! :-)
0
 
Anthony PerkinsCommented:
>>Who's bright idea with in the SQL server team was it, to prevent the use of RAISERROR within functions?<<
Why don't you ask them?  And while you are at it you may want to ask the Sybase team, as well.

>>They should offshore SQL Server dev to india<<
Why bother they hire they best minds fron the sub-continent to go to the U.S.

>>if they make mistakes like that!<<
What makes you think it was a mistake.  Is there some SQL Server documentation that says that RAISEERROR is supported in a UDF?
0
 
mrgilbe1Commented:
@acperkins, that was a joke.  Refer ScottPletcher's comment above.

I'll put [joke] [/joke] tags in next time for the overly serious, since a smiley is not clear enough.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 7
  • 5
  • 3
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now