Renwhqb

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