Post reply

SSMS built in Snippets; do they feature value substitutions?

  • May 12, 2017 at 9:55 am

    #325588

    So I'm looking deeper at SSMS snippets, and I cannot get this specific example to work as expected.
    mostly all i see is it is another place to keep a library of example code.

    I've gone to the Snippets Manager, added a folder.
    I've created a few examples, where i copy and paste my most commonly used codes blocks into snippets;
    among the examples is the renamed single file Insertsnippet.snippet in that folder.

    My knee jerk reaction, based on other text editors is that, from what I've wrestled with, I don't seem to be able to substitute one value multiple times from the default definition, even though the xml seems to be parameterized
    My question is related to substituting one value multiple times, with whatever is highlighted.
    so say i have I've typed some Tablename like InvoiceDetails in SSMS
    What I was hoping to do is call a snippet, and everywhere the substitution parameter exists, it would use the highlighted value;
    what i really see, is that the value $selected$ is substituted only once, and not even the first occurrence of the variable, either.
    so if the definition of my snippet was something like this.
    INSERT INTO [$selected$]
      SELECT *
      FROM [Stage$selected$] MySource
      LEFT OUTER JOIN [$selected$] MyTarget
       ON MySource.[PK] = MyTarget.[PK]
       AND MySource.[Col2] = MyTarget.[Col2]
      WHERE MyTarget.[PK] IS NULL

    my results end op being this:

    INSERT INTO []
      SELECT *
      FROM [Stage] MySource
      LEFT OUTER JOIN [InvoiceDetails] MyTarget
       ON MySource.[PK] = MyTarget.[PK]
       AND MySource.[Col2] = MyTarget.[Col2]
      WHERE MyTarget.[PK] IS NULL

  • May 12, 2017 at 4:18 pm

    #1942409

    Pretty sure the issue is just how you are doing your declarations and naming the variables. The declarations need to match the variables. So you have no literals declared as $selected$ so it's not going to do anything.
    If I used something like yours for an example and wanted the selected variable to be TableName and just put in that one table name and have the others automatically populate, it would looks something like:
    <Declarations>
               <Literal>
                <ID>TableName</ID>
                <ToolTip>Name of the Table</ToolTip>
                <Default>TableName</Default>
               </Literal>
              
        </Declarations>
        <Code Language="SQL"><![CDATA[
    INSERT INTO [$TableName$]
      SELECT *
      FROM [$TableName$] MySource
      LEFT OUTER JOIN [$TableName$] MyTarget
       ON MySource.[PK] = MyTarget.[PK]
       AND MySource.[Col2] = MyTarget.[Col2]
      WHERE MyTarget.[PK] IS NULL
    ]]>

    And then when I insert the snipped it would look like this:
    INSERT INTO [TableName]
      SELECT *
      FROM [TableName] MySource
      LEFT OUTER JOIN [TableName] MyTarget
       ON MySource.[PK] = MyTarget.[PK]
       AND MySource.[Col2] = MyTarget.[Col2]
      WHERE MyTarget.[PK] IS NULL

    And that first TableName after the INSERT INTO would be highlighted. Then I just type the table name in that first highlighted one, click outside of the highlight and the rest of the TableName variables populate with whatever I typed for TableName in the first line.

    Sue

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply