SQL Interpolated Strings

Question

Does T-SQL support Interpolated String?

Let's put up this example:

SET @query = 'SELECT ' + @somevariable + ' FROM SOME_TABLE'

I want to be able to do something like that:

SET @query = 'SELECT {@somevariable} FROM SOME_TABLE'

Thanks for the answers!

Solution 1

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

Solution 2

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

Solution 3

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

Comments

[Admin]

Thanks for your answer! But unfortunately i found limitation with this answer. It's worth to mention that xp_sprintf is limited to 254 characters :( So long queries won't work with that.

[j.f.sebastian]

Sorry - thats annoying. I just found another function that could help - FORMATMESSAGE - docs.microsoft.com/en-us/sql/t-sql/functions/…

[j.f.sebastian]

According to the FORMATMESSAGE documentation the limit is 2047 characters - hope that is enough for your requirements!

[Admin]

FORMATMESSAGE looks promising, but it supports only SQL versions >= 2016. I'm using the 2008 community edition, so it can only support the build in messages, which means, having my own string as first parameter won't work. Thanks for taking the time posting these links tho! I can confirm since i have also SQL 2017 server and 2008 installed.

[Admin]

Found a workaround for that, even tho It's a bit ugly. We need to add custom message manually and then we can use the custom message number: EXEC sp_addmessage @msgnum=50001, @severity=16, @msgtext=@query, @replace='replace' Do you mind posting it as another answer so i can mark as accepted? Thanks a ton for your help :)

[j.f.sebastian]

thanks for credit. Didn't have time to come back and edit my answer yesterday - you should mark this as accepted answer as it gives the detail!

[Jeroen Mostert]

The sp_addmessage approach has the important drawback that it is not safe when executed concurrently from multiple sessions. This can lead to some pretty confusing messages. The technique is still useful, but care should be taken that it is not used whenever you need interpolation, as that would increase the chances of a clash considerably. Also, from SQL Server 2012 onwards, there is CONCAT to simplify cobbling together strings without having to add CONVERTs everywhere.

[Admin]

Thank you sebastian! That's a good idea, done. @JeroenMostert, an example would be amazing please. Thanks

[KekuSemau]

It appears FORMATMESSAGE(string, ...) only works from SQL Server 2016 (13.x).

[cuongle]

beautiful format, that's what I need, thanks