Function to get current value or default from a string of two values I have SQL scripts that I want to be executable directly in the editor (like DataGrip or SSMS) and also in automated integration tests. However, in each use-case they require different environment and version parameters. I cannot use string_split because we use an ancient SQL Server 2012.

In order to differentiate between them, I created the GetCurrentOrDefault function that can either use the first or the second value. 

I start by finding the index of the | that I use as a separator. Then I get the string before and after this character. Finally, I check whether the string has the format '{%}|%' and if yes, then I use the default version, otherwise the current one. (All magic-numbers are arbitrary primes.)

if object_id('dbo.GetCurrentOrDefault') is not null drop function GetCurrentOrDefault

go

create function GetCurrentOrDefault(@valueOrDefault nvarchar(59)) returns nvarchar(17)
begin
    declare @current_value as nvarchar(17);
    declare @default_value as nvarchar(17);
    declare @value_separator_index as int

    select @value_separator_index = charindex('|', @valueOrDefault)

    if @value_separator_index = 0 return null

    select @current_value = substring(@valueOrDefault, 0, @value_separator_index)
    select @default_value = substring(@valueOrDefault, @value_separator_index + 1, len(@valueOrDefault) - @value_separator_index + 1)
    return iif(@valueOrDefault like '{%}|%', @default_value, @current_value)
end

go


Usage example:

declare @env as nvarchar(51) = N'{Environment}|production'
declare @ver as nvarchar(59) = N'{Version}|3.9.0'

select @env = dbo.GetCurrentOrDefault(@env)
select @ver = dbo.GetCurrentOrDefault(@ver)

if @env is null raiserror ('Invalid environment: ' + @env, 16, 1)
if @ver is null raiserror ('Invalid version: ' + @ver, 16, 1)

-- many many inserts with settings ...


Tests replace the placeholders with their custom values like:


.GetSql().Format(new { Environment = "test", Version = "4.0.0" }) // C#





What do you think? Is there a more clever solution?
Potential Issues


a naive string replacement could change more than just the placeholders; for instance, when another part of the script uses the same string as a literal
the replacement can introduce a value with the same format {%}; your sql has no way of knowing that this is a replacement value, and since the format matches that of a place holder, it will be ignored and the default value will be taken
make sure the sql user of your unit test project has limited rights, because you don't know the exact sql that is going to run at runtime


If you insist on using a string replacement, try to manage escape characters and try to avoid false positives/negatives, both from sql and C# injection.