Function to get current value or default from a string of two values

Question

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?

Answer 1

Answering your question

Overall, I think the solution is sound, but there are a few things you could make cleaner. I agree with everything in dfhwze's answer, but assuming this is the route you keep here are a few thoughts:

This piece is a little risky in case N'|' exists in the new value.

SELECT @value_separator_index = CHARINDEX( '|', @valueOrDefault );

You probably want the last N'|' in the value (didn't test this; I probably have an off-by-one error or missing zero-handling).

SELECT @value_separator_index = LEN( @valueOrDefault - CHARINDEX( N'|', REVERSE( @valueOrDefault ) ) )

You can also provide an expected label to the function to avoid an issue that dfhwze calls out.

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

Then you can make sure the Label doesn't have unexpected values by comparing it with... another pattern

IF @Label LIKE N'%[%]%' 
   OR @Label LIKE N'%[_]%' 
   OR @Label LIKE N'%[\[]%' ESCAPE '\' 
   OR @Label LIKE N'%[\]]%' ESCAPE '\'
BEGIN
  -- Do stuff, like replace the values and escape them
END;

From this point, your final validation becomes (assuming you use \ to escape things), which avoids the issue of them providing a strange non-default value

RETURN IIF( @valueOrDefault LIKE '{' + @Label + '}|%' ESCAPE '\', @default_value, @current_value)

---

Disputing your premise, and providing an alternative

Once you peel back the string parsing, you can realize that this effectively becomes ISNULL

SET @environment = ISNULL( <<maybePopulated>>, N'production' )

You could accomplish this like so:

DECLARE @configuredEnvironment nvarchar(17);
DECLARE @defaultEnvironment nvarchar(17) = N'production'
DECLARE @environment nvarchar(17) = ISNULL( @configuredEnvironment, @defaultEnvironment );

Then if your test runner can just make sure to set @configuredEnvironment in the task (this might be as easy as dropping the first row of the file and replacing it with your configuration) and you don't have to do string parsing.

---

Providing another alternative

Ultimately, what you're really trying to do is create parameterized SQL with default values. As it turns out, this is a concept that already exists:

CREATE PROCEDURE dbo.[test my cool thing]
( @environment nvarchar(17) = N'production',
  @version nvarchar(17) = N'3.9.0'
)
AS
BEGIN
  -- do something cool
END;
GO

From your test runner, this then becomes

using (var conn = new SqlConnection(connectionString)) // Can be pulled from a config file
{
    conn.Open();
    using (var cmd = new SqlCommand("dbo.[test my cool thing]", conn))
    {
        // I forget the exact properties, but this should be close
        cmd.Parameters.AddWithValue("@environment", environment); // config file
        cmd.Parameters.AddWithValue("@version", version); // config file
        cmd.CommandType = CommandType.StoredProcedure;
        
        cmd.ExecuteNonQuery();
    }
}

Then for end-users running it in the IDE it just becomes

EXECUTE dbo.[test my cool thing] -- Pass parameters if you want them

Answer 2

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.