SQL Interpolated Strings

For readability when your strings are long I like doing this:

SET @query = replace( replace( replace( 
               'SELECT {@variable1} FROM {@variable2} WHERE {@variable3}'
               , '{@variable1}', @variable1 )
               , '{@variable2}', @variable2 )
               , '{@variable3}', @variable3 )

It has the advantage of readability of the SQL string plus you can use the same variable multiple times. Clearly it's not proper interpolation but I like it better than the FORMATMESSAGE() option since

• It doesn't have a length limitation. Who writes queries shorter than 2047 characters anyway?! ;)
• It supports named placeholders instead of just ordered ones
• Supported in any version of SQL Server
• You can use the same placeholder multiple times

It's not a common way of doing things in T-SQL but it is possible with xp_sprintf

    DECLARE  @query AS VARCHAR(100)
            ,@somevariable as VARCHAR(10) = '[id]'
    EXEC xp_sprintf @query OUTPUT, 'SELECT %s FROM SOME_TABLE', @somevariable

    PRINT @query

Giving the credits to @j.f.sebastian for pointing out these solutions. Sadly xp_sprintf is limited to 254 characters, so that wouldn't be ideal when using long queries. FORMATMESSAGE instead is limited to 2047 characters, so that's good enough in order to run long queries.

I'll summarize everything in one post for the solutions in order to keep things organized.

Answer 1:

Using FORMATMESSAGE it's important to know, that using interpolated string as first the parameter, Its supported only SQL versions 2012 and above, so I'll post 2 answers with FORMATMESSAGE:

SQL Version >= 2012:

SET @query = FORMATMESSAGE('SELECT %s FROM SOME_TABLE', @somevariable);

SQL Version < 2012:

EXEC sp_addmessage 50001, 16, 'SELECT %s FROM SOME_TABLE', NULL, NULL, 'replace'
SET @query = FORMATMESSAGE(50001, @somevariable)

Answer 2:

Using xp_sprintf stored procedure is important to note that It's limited to 254 characters, so it won't be a good idea for long queries.

DECLARE  @query AS VARCHAR(100)
        ,@somevariable as VARCHAR(10) = '[id]'
EXEC xp_sprintf @query OUTPUT, 'SELECT %s FROM SOME_TABLE', @somevariable