SQL Prompt Tips–Using $surroundtext$ in a Snippet

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.