Create table with synonyms

• richardmgreen1

  SSChampion

  Points: 10400

  More actions

  April 15, 2019 at 12:18 pm

  #3372267

  Hi all

   

  I've having a bit of a tidy up of our functions as we have the same function all over the place (same name/code/etc.).

   

  The function (for grabbing the name of any UK bank holidays) is part of our DIM_Date table.

   

  The function is called "fn_Get_UK_Holiday" and that appears in several databases.

  I've created the same function in our Utilities database so we only have one place to adjust any code.

   

  I've created a synonym called "fn_Get_UK_Holiday2" to point to the above function.

   

  When I try to create a seconday (for test purposes) date table using this synonym, I get the following error:-

  "Synonyms are invalid in a schemabound object or a constraint expression."

   

  The code for the function is :-

  CREATE FUNCTION [dbo].[fn_Get_UK_Holiday]

  (

  @Date DATE

  )

  RETURNS VARCHAR(50)

  AS

  BEGIN

  DECLARE @HolidayName VARCHAR(50)

  SELECT

  @HolidayName = HolidayName

  FROM

  tbl_UK_Holidays

  WHERE

  pkHolidayDate = @Date

  RETURN @HolidayName

  END

   

  We use the function to stop LEFT JOINing onto another table every time we write code where we want to exclude bank holidays.

   

  As this is part of a CREATE TABLE statement, has anyone any ideas on how to get around the error?