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:

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.

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.

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

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).

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.

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.

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.

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.

Filed under: Blog Tagged: Redgate, SQL Prompt, syndicated, T-SQL