Call a UDF if it exists

• Jos Janssen

  Old Hand

  Points: 322

  More actions

  July 30, 2007 at 4:00 am

  #179915

  Hello all,

  I'm writing a stored procedure in a database to fill a calendar table with 1/0 according to workingday or not. The application will be available for other sites of our company which run it in their own SQL database.

  In our own SQL Server we have a UDF fn_IsWorkingDay in the MASTER database which also checks for national holidays. I want to call this function in the sp, but only if it is defined in the Master database, because other sites might not have it that way.

  So I use code like this:

  DECLARE @InclHolidays BIT

  SELECT @InclHolidays = CASE WHEN EXISTS (

  SELECT * FROM Master.INFORMATION_SCHEMA.ROUTINES

  WHERE SPECIFIC_SCHEMA = N'dbo'

  AND SPECIFIC_NAME = N'fn_IsWorkDay' )

  THEN 1 ELSE 0 END

  and somewhere after that

  CASE WHEN @InclHolidays = 1

  THEN

    CASE

    WHEN Master.dbo.fn_IsWorkDay(@DateFrom) = 1

    THEN 1 ELSE 0 END

  ELSE

    CASE

    WHEN DATEPART(dw, @DateFrom) > 5

    THEN 0

    ELSE 1

    END

  END

  But regretfully this won't run if fn_IsWorkDay does not exist.

  Invalid object name 'Master.dbo.fn_IsWorkDay'

  Has anyone any suggestion to work around this problem using T-SQL?

  All help is highly appreciated!

  Regards

  Jos Janssen

• Crispin Proctor

  SSCoach

  Points: 18161

  More actions

  July 30, 2007 at 6:25 am

  #723269

  it will fail at parse time. Before a query starts running, the parser will check to ensure all objects and syntax is valid.

  Why are you using a UDF? They will slow your code down. Rather move the logic into the query. More so, why are would it not exist?

  Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!