0

I opened a SQL script in Visual Studio containing template parameters (as they are used by SSMS usually), but Visual Studio seems to ignore them.

Some background:

In SQL Server Management Studio (SSMS), I can prepare a T-SQL script as follows and then press Ctrl+Shift+M to open a dialog, where I can enter values for the variables (syntax is: <Name, Datatype, default> as described here, and here is a more detailed description how template parameters can be used):

Template Parameters in SQL Management Studio

Example:

DECLARE @UserLastName NVARCHAR(max)='<Last name,nvarchar(max),>';

Here, the <...> bracket expressions will be replaced by the values you entered in the dialog. If you type Doe and click OK in the dialog, you will get

DECLARE @UserLastName NVARCHAR(max)='Doe';

inserted into the script afterwards. This is quite useful if you create scripts for repetitive manual tasks, because you can type the values into the dialog and get a running script for that case.

Instead of the keyboard shortcut, you can use the menu option

Menu Option

The issue:

I tried to do the same in Visual Studio 2017, where I have installed Microsoft SQL Server Data Tools. But the keyboard shortcut does not work there, and there seems to be no such menu option.

Do you know how to invoke this dialog in Visual Studio?

Improve this question
6
  • VS 2017 != SSMS, despite the superficial similarities in the UI. There is no such option there, and there is no way to invoke that dialog because, well, nobody wrote code for such a dialog -- the whole template function isn't there. If you switch the script to SQLCMD mode (which can be done in either application) you can use those variables, which are, however, not nearly as convenient. Visual Studio has its own approach in the form of code snippets, which are also not quite equivalent. Commented Feb 20, 2018 at 15:22
  • @JeroenMostert - Good hint, thank you. Yes, I am afraid, that function does not exist in Visual Studio. I have seen that a SQLCMD is there in VS, but you are right, it is not as convenient as the templates. I was looking for something you could instantly use for existing SQL templates. Code snippets are also not very convenient to create, and as far as I know, they exist only for VS, not for SSMS. Commented Feb 20, 2018 at 15:32
  • At build time, you actually could solve this fairly easily with a T4 template, but as a simple "cut and paste and fill in some values at runtime", I don't think there's anything. Nor is it even necessarily easy to build, since the SQL editor is its own thing and I don't think there's any convenient hook for inserting extensions, like you have for .NET languages with Roslyn. I'm hesitant to put any of this up as an answer in case someone finds something amazing. Commented Feb 20, 2018 at 15:35
  • @JeroenMostert - that's true. I know T4 templates especially from the EDMX background (Entity Framework), and they are mostly used inside the C# / VB.NET world. Commented Feb 20, 2018 at 15:37
  • I just verified that you can actually use them inside a database project (if you explicitly configure the .tt file to use the TextTemplatingFileGenerator custom tool, and use an output directive to produce a file with a .sql extension). But this is academic since I presume you want to supply the template values manually, and not as part of a build step. Commented Feb 20, 2018 at 15:46
Related questions
3
How to create a new stored procedure template for Visual Studio?
4
How to create item templates for SQL files, for Visual Studio Database Projects
0
Why I can't use template table in dynamic query SQL SERVER 2005
Load 7 more related questions Show fewer related questions

0

Reset to default

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.