[dba-SQLServer] Raising Error in Function

David Emerson newsgrps at dalyn.co.nz
Thu Nov 8 20:09:22 CST 2012


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

 

 

 



More information about the dba-SQLServer mailing list