can you RAISERROR() in a function?

• Nikola Tesla-229239

  Old Hand

  Points: 329

  More actions

  April 4, 2006 at 4:39 pm

  #167800

  hey guys, i am trying to write a function that will calculate the correct price of an item based on the customer type. however, if the (integer) value of customer type is invalid, i want SQL to throw an exception. but it seems as if RAISERROR is not valid in a function? is there a work around to this?

  Thanks for any ideas and help!

  ~Dan

  ======================================

  CREATE FUNCTION fn_CustomerPrice(

  @PartNumber_Or_UPC AS VARCHAR(255),

  @CustomerType AS INTEGER)

  RETURNS Money AS

  BEGIN

  --TEMP VARIABLES

  DECLARE @PriceLevel AS INTEGER

  DECLARE @FinalPrice AS MONEY

  --FIND OUT WHAT PRICE LEVEL TO USE BASED ON THE CUSTOMER TYPE

  SELECT @PriceLevel = PriceLevel

  FROM AvailableCustomerTypes

  WHERE CustomerType = @CustomerType

  IF @PriceLevel IS NULL

  RAISERROR ('Function dbo.fn_CustomerPrice() could not find a Price Level for this customer.', 18, 1, @CustomerType, @PartNumber_Or_UPC)

  IF @PriceLevel = 1

  ...

  IF @PriceLevel = 2

  ...

  IF @PriceLevel = 3

  ...

  RETURN @FinalPrice

  END

  ---

  There are ll types of people: those who understand Roman Numerals, and those who do not.

• Jeff Gray

  SSChampion

  Points: 10667

  More actions

  April 5, 2006 at 6:53 am

  #630960

  Although this is not a very good approach, it should work.  You could divide by zero.  That will raise an error.

  There's got to be a better way, however.