David Emerson
newsgrps at dalyn.co.nz
Fri Nov 9 16:44:14 CST 2012
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