Create table with synonyms

  • richardmgreen1

    SSCrazy Eights

    Points: 9716

    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?

Viewing post 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply