4

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:

enter image description here

SQL within \Shared\CommonVariables:

enter image description here

Within [p_An_Example]:

enter image description here

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

-

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

enter image description here

"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

Improve this question
3
  • Possible duplicate of Is it possible to make SQLCMD Mode 'Sticky' in Database Projects Commented Aug 14, 2018 at 11:12
  • 1
    The T-SQL source code used to build the model may contain SQLCMD variables but not SQLCMD commands, like :r.. I'm not aware of a way to include files during the build process that become part of the CREATE PROCEDURE` statement. Commented Aug 14, 2018 at 11:56
  • @DanGuzman Yes I've checked and I do have SQLCMD mode on, so this must be the issue... its just not possible within SPs. Thank you for the confirmation Commented Aug 14, 2018 at 12:50

2 Answers 2

Reset to default
16

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

enter image description here

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Turns out I do have this option enabled, but as DanGuzman said in the comments it turns out SQLCMD commands aren't available within CREATE PROC statements
@SamuelO'Connor Yes, that's correct. They can't be within the definition of an object (as I mention in the answer), as the DBMS itself doesn't have SQLCMD mode. When using SQLCMD statements it's the application (VS/SSMS) that interprets and deals with them, not the DBMS.
11

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:

enter image description here

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.