Write a SQL function with that work based on the SQL Server version

Question

We have clients that are running on SQL Server 2008 and 2012.

I want to write a function that is going to use Format function. I have a custom function that is doing almost same thing as Format in SQL Server 2008, but its performance is worse than Format.

I wanted to make it conditional so if it's running on SQL Server 2012, then it would use the built-in T-SQL Format and if it's on SQL Server 2008, then it use my own custom function.

So I wrote a function like this:

CREATE FUNCTION [system].[FunctionName1]
(
    @param1 VARCHAR(MAX),
    @param2 VARCHAR(MAX)
)
RETURNS nvarchar(Max)
AS
BEGIN

    DECLARE @SQLVersion VARCHAR(16) = CONVERT(VARCHAR(16), SERVERPROPERTY('ProductVersion'))

    SET @SQLVersion = LEFT(@SQLVersion, ISNULL(NULLIF(CHARINDEX('.', @SQLVersion, 1 + CHARINDEX('.', @SQLVersion)) - 1, - 1), 0))

    DECLARE @Return DECIMAL(4, 2) = CASE 
            WHEN ISNUMERIC(@SQLVersion) = 1
                THEN CONVERT(DECIMAL(4, 2), @SQLVersion)
            ELSE 0
            END
    IF @Return > 11.0
    BEGIN 
        RETURN Format(@param1, @param2) 
    END
    ELSE
    BEGIN
        RETURN Format_Custom(@parm1, @param2)
    END

    RETURN NULL
END

The problem is in SQL Server 2008: the format function is unknown. So it throws a syntax error when I want to deploy it. I already have a work around for it with deployment script. But I want to know can I somehow with sql built-in functions pass the Format line or ignore it?

Answer 1

No, it can't be done in a function.

It has to be able to compile the code before any of your logic can execute, and it's failing at that point because there's a function it doesn't know of.

And you can't apply the usual work-around to get past the compilation error - putting the not-certain-to-compile code in as a string and compiling/executing it separately using an EXEC call - since you're not allowed dynamic SQL in UDFs.