[AccessD] Raising Error in Function

Hi Jim,

I can get the Raise error response to work with stored procedures but not
functions.

I am not able to even save the function if it has the raise error line in it
(and hence not able to call it from a sproc)

David

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Saturday, 10 November 2012 3:08 p.m.
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Raising Error in Function

Can you get the 'Raise error' response to work by itself and/or work with
another function?

Is the function caller setup to receive and manage the error response?

Jim  

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David Emerson
Sent: Friday, November 09, 2012 2:44 PM
To: AccessD
Subject: [AccessD] Raising Error in Function

No replies from the SQL group so I thought I would try here.

 

I have the following function in SQL 2012:

 

create FUNCTION [dbo].[fnLegStatusToBookingEventStatus]

            (

                        @LegStatus varchar(10)

            )

            RETURNS int

AS 

            BEGIN 

 

                        DECLARE @BookingEventStatus int

 

                        SELECT @BookingEventStatus = 

                                    CASE

                                                WHEN @LegStatus = 'ACTV'
THEN 0

                                                WHEN @legStatus = 'CANX' OR
@LegStatus = 'RFND' THEN 1

                                                ELSE -1

                                    END

 

                                    IF @BookingEventStatus = -1 

                                                RAISERROR('No list found
with name %s', 16, 1, @LegStatus)

                        

                        RETURN(@BookingEventStatus) 

            END

 

When I try to execute it I get the following error:

 

Invalid use of a side-effecting operator 'RAISERROR' within a function.

 

Is it possible to raise an error within a function?  I would like to have
some warning if @LegStatus is not one of the items in the Case statement.

 

If I can only use a stored procedure the how can I call it from within a
statement such as:

 

      SELECT dbo.fnLegStatusToBookingEventStatus(legtostatus) AS Status,
scID AS SourceRecordId

      FROM dbo.ttmpTranzImportBooking

 

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand

 

 

 

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com