April 15, 2019 at 12:18 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy