Using EXECUTE with FORMATMESSAGE [duplicate]

Question

I’ve learned that FORMATMESSAGE() is the closest thing to parameterising a string in SQL Server. So this works for me:

DECLARE @db NVARCHAR(12) = 'test';
SELECT formatmessage(N'USE master; DROP DATABASE IF EXISTS %s;', @db);

I also know that you can execute a constructed string using EXECUTE():

DECLARE @db NVARCHAR(12) = 'test';
EXECUTE(N'USE master; DROP DATABASE IF EXISTS ' + @db);

It seems to me that you should be able to combine the two as follows:

DECLARE @db NVARCHAR(12) = 'test';
EXECUTE(formatmessage(N'USE master; DROP DATABASE IF EXISTS %s;', @db));

That doesn’t work. I get the error message:

Incorrect syntax near 'formatmessage'.

Is there a trick to using FORMATMESSAGE with EXECUTE?

Note

This question has been closed because somebody thinks it’s the same as Why does concatenating strings in the argument of EXEC sometimes cause a syntax error in T-SQL? .

The solution may be the same (EXECUTE doesn’t allow expressions with function calls) but:

• The question is different (concerning FORMATMESSAGE() rather than concatenation), so anybody looking for a solution (such as I) won’t naturally think that the other question is relevant.
• Neither the answer to the other question nor the referenced documentation makes it clear that the string can be evaluated, such as concatenation, but not if it includes a function call.

Answer 1

You're on the right track but in SQL Server, EXECUTE() does not validate expressions like FORMATMESSAGE(), as these are not allowed directly. But to use them both together a variable can be used to hold the result of FORMATMESSAGE() first, then pass that variable to EXECUTE().