3
\$\begingroup\$

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?

\$\endgroup\$
  • 1
    \$\begingroup\$ Is there a reason the test can't have a parameter with a default value? e.g. if your test is a stored procedure, it can't have @Environment nvarchar(51) = N'production' as a parameter? Your last piece suggests that your test runner is in some other language, maybe C#? Can you use parameterized SQL in the test runner to handle this? \$\endgroup\$ – Dannnno Aug 22 at 14:10
  • 1
    \$\begingroup\$ @Dannnno mhmm... theoretically I could use such paremeterized SQL but I guess I then would need to have some kind of a stored procedure for that. Currently it's just a flat file with many inserts. Your guess about C# was correct. I added a comment for this. I like being able execute scripts directly and via code but this is tricky as you need some fallback values when running it in an IDE. \$\endgroup\$ – t3chb0t Aug 22 at 14:25
  • \$\begingroup\$ .GetSql() gets you the entire script? or just the declarations of variables.. \$\endgroup\$ – dfhwze Aug 22 at 15:22
  • 1
    \$\begingroup\$ @dfhwze tl;dr: the entire script. It's defined in the main project and I linked it in a test project under a different name as an embedded resource - can it be more complex? :-P I then import it, inject the two values and execute the script to insert these settings with a different environment and/or version. After that I run tests. \$\endgroup\$ – t3chb0t Aug 22 at 15:36
  • 1
    \$\begingroup\$ You can replace if object_id('dbo.GetCurrentOrDefault') is not null drop function GetCurrentOrDefault with just DROP FUNCTION IF EXISTS [dbo].[GetCurrentOrDefault]. It also fixes a potential bug with the lack of schema (dbo ) on the second part of your statement. And it fixes another obscure potential bug if GetCurrentOrDefault happens to be anything other than a function. \$\endgroup\$ – Brad M Sep 3 at 17:49
3
\$\begingroup\$

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
\$\endgroup\$
  • \$\begingroup\$ Mind blowing! ISNULL looks really cool. \$\endgroup\$ – t3chb0t Aug 22 at 16:23
3
\$\begingroup\$

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.

\$\endgroup\$
  • 1
    \$\begingroup\$ I might try the ugly python style of special variable names like ____Version____ or should I use more underscores? I gues python fans would be thrilled, the more the better ;-] \$\endgroup\$ – t3chb0t Aug 22 at 15:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.