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?