Can I use SQLCMD Commands in Visual Studio Database Project Stored Procedure?

Question

I have a lot of SQL that is the same over multiple stored procedures.

For example most procedures have the same variables declared, and is in the same try catch block to handle errors.

I'd like to use the :r command so that I can write this code in one file and just import it into each sp. I can use that command in the pre and post build scripts but I can't get it working within a normal 'Build' type database object.

Example:

SQL within \Shared\CommonVariables:

Within [p_An_Example]:

The error just says "SQL46010: Incorrect Syntax near \."

-

If I wrap the path in quotes that error is replaced by one on 'BEGIN':

"SQL46010: Incorrect Syntax near BEGIN."

-

Is there anything I can do to get this working? At the moment [p_An_Example] has the Build Build Action property, and \Shared\CommonVariables is set to None.

(I'm using Visual Studio 2017, the database project is pointing to a 2008 database, SQLCMD is installed etc)

Edit: Not a duplication because I do have SQLCMD mode on... but it turns out SQLCMD commands are not available within stored procedure statements

Answer 1

Yes, the option is in SQL -> Execution Settings -> SQLCMD Mode:

Note, however, that you can't include sqlcmd code within an SP, as the DBMS can't interpret the code, it's purely for using in a coding environment; such as within SSMS/VS.

Answer 2

In Visual Studio 2019, you can do the same by opening the file with the SQLCMD statements, then click the last icon in the new view to make the errors go away: