Tag: SQL Prompt

A user on the SQL Community Slack was asking about what the $surroundtext$ variable. This post looks at how this can be used in snippets.

This is part of a series of posts on SQL Prompt. You can see all my posts on SQL Prompt under that tag.

A Scenario

I find that I want to convert some inline SQL to a stored procedure. We have a lot of code in an application that looks like this:

SELECT 
     SUM(sod.OrderQty) OVER(ORDER BY sod.SalesOrderID, sod.ProductID) AS Total
  FROM Sales.SalesOrderDetail AS sod
  WHERE sod.ProductID = <somevalue>

The application replaces <somevalue> with an actual value and runs this code. This potentially is a SQL Injection vector, but this also isn’t easily tuned on the server, and can get copied and pasted into different places in the code. It would be better to have this as a stored procedure.

Make the Conversion Easy

To make this a stored procedure, I would want this query to look like this:

CREATE PROCEDURE dbo.GetGroupedSales
     @Id INT
AS
BEGIN
SELECT 
     SUM(sod.OrderQty) OVER(ORDER BY sod.SalesOrderID, sod.ProductID) AS Total
  FROM Sales.SalesOrderDetail AS sod
  WHERE sod.ProductID = @id

END

I can create a snippet that looks like the skeleton of a stored procedure with this code:

create procedure $procname$
$param1$ $paramdt$
as
begin
$SELECTEDTEXT$
end
go

I’ve got a screenshot of this below, showing some default values for the various parameters. This makes it easy for me to build a proc. However, there is one variable that isn’t in the list: $SELECTEDTEXT$.

This variable will take any text that is selected in SSMS (or VS) and put it inside of the snippet in that location specified.  That will help us wrap our query with the other code in the snippet.

Here is my snippet:

Using the Snippet

Let’s see this in action. In SSMS, I have highlighted my query.Notice the little Prompt popup near the cursor.

When I see this, I can hit the CTRL key and I’ll get a drop down list. I will type “mp” which is my snippet code.

This finds my snippet. I can hit Tab and my snippet is inserted, with my default variable values and also the text I selected in the place where $SELECTEDTEXT$ was in the snippet.

Now like any other snippet, I can tab between the variables and change them. When I’m done, I hit Enter and I have my code.

Now I just need to save this in version control and deploy it to my production system.

If you haven’t tried SQL Prompt, download the eval and give it a try. I think you’ll find this is one of the best tools to increase your productivity writing SQL.

Video Walkthrough

I made a video of using $surroundtext$ that you can watch. All my SQL Prompt tips are in this playlist.

 

A few years ago SQL Prompt added a command palette to let you search the commands available. This is similar to the same concept in Visual Studio Code, ADS, and various other tools. This post looks at how to get to this tool in SSMS.

This is part of a series of posts on SQL Prompt, which is an amazing productivity tool from Redgate Software.

Opening the Palette

You can also open the Command Palette from the menu. In the SQL Prompt menu, it is the first item. You can also see there is a shortcut of ALT+S below here.

When I click this, I’ll see the full list of palette commands. This is a large window, and across the top I can filter things. In the complete list, you see various things: objects, snippets, and refactoring commands. We also have the Prompt behavior settings as well.

For example, I could look for things with “brackets”. I see a few things that are relevant. I can pick any of them if I want that apply to my code or change the behavior of the tool

Adding to the Menu

If you can’t remember the shortcut. or don’t want to use the menu, you can add this as a button in the toolbar. Here’s the easy way. When you installed SQL Prompt (or the Toolbelt), there is a Redgate toolbar added. Mine looks like this:

I clicked the “Add or Remote buttons ” and got a list of buttons on this toolbar. I can click “Customize if I like. That opens the dialog below.

Now click “Add Command”. That gives you a list of items in all the menus in SSMS. Pick the SQL Prompt menu on the left (first one). Then scroll down and find “Open command palette” in the right side. Select it and click OK.

Now you have this in your toolbar.

Now one click, and you have the palette.

FWIW, you can use this trick to add any menu item to a toolbar that you want.

UPDATE: A comment asked about partial matches, and you can see I see partial matches for “Addre”.