Quick Prompt Tips–Custom Procedure Templates

One of the things that I often do is create stored procedures. The syntax for doing so is simple, but it has a number of items that need to be included. SQL Prompt makes this much quicker with the “cp” snippet. When I type “cp”, I get this:

2016-09-13 13_01_43-SQLQuery1.sql - (local)_SQL2016.AlwaysEncryptedDemo (PLATO_Steve (64))_ - Micros

I can hit Tab and I have a snippet, but it has a lot of things I don’t like in it. Plus, I want to save time coding, not have to remove some commented out items.

2016-09-13 13_06_17-SQLQuery1.sql - (local)_SQL2016.AlwaysEncryptedDemo (PLATO_Steve (64))_ - Micros

Let’s make this more efficient. I can go to the Snippet Manager under the SQL Prompt menu and select it. When it opens, the snippets are highlighted, so I type “cp” to get to the Create Procedure snippet.

2016-09-13 13_36_11-SQLQuery1.sql - (local)_SQL2016.EncryptionDemo (PLATO_Steve (64))_ - Microsoft S

I click edit and see the code, which I highlight before deleting this.

2016-09-13 13_36_45-SQL Prompt - Edit Snippet

Then I paste in the code that makes more sense to me. Notice that in my case, I have two placeholders, not one (as shown above).

2016-09-13 13_37_03-SQL Prompt - Edit Snippet

The code I use has a header in the procedure, and the procedure name is used both for the definition and a GRANT EXECUTE. I include the begin..end structure for the procedure with the cursor starting in the spot where I’d put code. I also have a placeholder for a role name. It looks like this.

CREATE PROCEDURE $procedure_name$

/*
Description:

Changes:
Date       Who Notes
———- — —————————————————
*/
AS
BEGIN
$CURSOR$
END
GO

GRANT EXECUTE ON $procedure_name$ TO $role_name$

In practice, when I type “cp” and hit Tab, I get the code with the procedure highlighted. I can enter a name here. Note what I typed is also placed in the GRANT statement at the bottom.

2016-09-13 13_40_02-SQLQuery1.sql - (local)_SQL2016.EncryptionDemo (PLATO_Steve (64))_ - Microsoft S

Once I am done and hit Tab, my cursor jumps to the next placeholder, in this case, the role name. Notice that SQL Prompt knows this is a role and gives me a list of roles and users to choose from.

2016-09-13 13_40_44-SQLQuery1.sql - (local)_SQL2016.EncryptionDemo (PLATO_Steve (64))_ - Microsoft S

When I finish and hit Tab again, the cursor jumps to the point between the BEGIN and End where I will enter my code. Now my job begins.

2016-09-13 13_42_26-SQLQuery1.sql - (local)_SQL2016.EncryptionDemo (PLATO_Steve (64))_ - Microsoft S

This little customization gives all my procedures some standard look as well as ensuring that I can quickly build procedures without a lot of mundane, tedious typing.

Try out this quick SQL Prompt tip and see how much smoother your coding goes. And if you’re not a SQL Prompt user, download an evaluation today and see how much more efficient you can be when writing T-SQL code.

You can see a complete list of SQL Prompt tips at Redgate.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , , . Bookmark the permalink.