SQL Server Stored Procedure Tutorial with sqlcmd and DbSchema

The same query sits in the reporting screen, in a nightly job, and in a support script, and the third copy of it is already out of date. Moving it into the database gives it one home, one name, and one set of permissions:

CREATE PROCEDURE schema_name.procedure_name
    @parameter_name data_type = default
AS
    sql_statement;

Call it with EXECUTE schema_name.procedure_name;, or EXEC for short.

What a stored procedure is

A stored procedure is a group of Transact-SQL statements stored in the database under a name, or a reference to a .NET common language runtime method. It takes input parameters, returns values through output parameters and result sets, and returns a status value that says whether it succeeded.

Four things follow from putting the statements on the server rather than in the application. The commands run as a single batch, so only the call crosses the network instead of every line of code. Users can be granted EXECUTE on the procedure without any permission on the tables underneath it, and because parameter input is treated as a literal value rather than executable code, it is harder for an attacker to inject a command into the statements inside. Changes to the query stay in the data tier, so the application doesn't have to be rebuilt when the table underneath it changes shape. And a procedure is compiled the first time it runs, after which the execution plan is reused.

The plan reuse is the one with an edge on it. Microsoft's stored procedures documentation notes that after significant changes to the tables or data a procedure reads, the precompiled plan can make the procedure slower rather than faster, and that recompiling it to force a new plan is the fix.

What a procedure body can't contain

CREATE PROCEDURE can't share a batch with other Transact-SQL statements, so it follows a GO and the body ends with one. Inside the body, a fixed list of statements is rejected anywhere it appears:

• USE database_name
• CREATE AGGREGATE, CREATE DEFAULT, CREATE RULE, CREATE SCHEMA
• CREATE or ALTER for a TRIGGER, FUNCTION, PROCEDURE or VIEW
• SET SHOWPLAN_TEXT, SET SHOWPLAN_XML, SET SHOWPLAN_ALL, SET PARSEONLY

Because USE is on that list, a procedure works in the database it was created in, and reaching another database means naming objects across it rather than switching context. Changes a procedure makes on a remote instance are outside your transaction: they can't be rolled back.

Three numbers bound the rest. A procedure takes up to 2,100 parameters. Procedures nest up to 32 levels deep, and going past that fails the whole calling chain, with @@NESTLEVEL reporting where you are. Parameters of type text, ntext and image can't be output parameters unless the procedure is a CLR procedure, and neither can a table-valued type.

One behavior surprises people at deployment time rather than at creation time: a procedure can reference tables that don't exist yet. Only the syntax is checked when you create it, and object names are resolved at the first execution, so a procedure over a misspelled table is created without complaint and fails when someone calls it.

Creating one requires CREATE PROCEDURE permission in the database plus ALTER permission on the schema it goes into, or membership in the db_ddladmin fixed database role.

SET NOCOUNT ON in a stored procedure

After every statement that changes or returns rows, SQL Server sends back a count message, which is an extra result set the caller has to read past:

(5 rows affected)

SET NOCOUNT ON stops those messages, and stops the DONEINPROC message the server sends for each statement in a procedure. It leaves @@ROWCOUNT alone: the function is still updated, so procedure logic that branches on the row count keeps working.

Where it goes is settled, and the reason for it is not. The CREATE PROCEDURE best practices put SET NOCOUNT ON as the first statement of the body, immediately after AS, to keep the output to a minimum, and add that "there is no measurable performance benefit however on today's hardware". The SET NOCOUNT reference still describes a significant boost for procedures that hold many statements or Transact-SQL loops, because the network traffic drops. Write it for the clean output, and measure before you claim anything else from it.

Types of stored procedures

Two naming rules come out of that table. Don't start your own procedures with sp_, which is the prefix of the system procedures shipped with the Database Engine. And keep the names short at the extremes: a global temporary procedure name including the ## can't exceed 128 characters, a local one including the # can't exceed 116. Extended procedures are on their way out, and the documentation asks you to write CLR procedures instead.

Two procedures created in sqlcmd

Connect the sqlcmd utility to the database the procedure belongs in:

sqlcmd -S your-server-name -d SampleDB -U your-username -P your-password

The examples run against one small table:

CREATE TABLE dbo.Products (
    ProductID   int           NOT NULL PRIMARY KEY,
    ProductName nvarchar(50)  NOT NULL,
    Price       decimal(10,2) NOT NULL
);

INSERT INTO dbo.Products VALUES
    (1, 'Apple',  0.50),
    (2, 'Banana', 0.30),
    (3, 'Cherry', 0.20),
    (4, 'Dates',  1.00);
GO

The first procedure takes no parameters and returns the catalog:

CREATE PROCEDURE dbo.GetAllProducts
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ProductID, ProductName, Price
    FROM dbo.Products
    ORDER BY ProductID;
END;
GO

EXEC dbo.GetAllProducts;
GO

A parameter turns that one query into a family of them. Give the parameter a default and the procedure can also be called with nothing at all:

CREATE PROCEDURE dbo.GetProductsUnder
    @MaxPrice decimal(10,2) = 1.00
AS
BEGIN
    SET NOCOUNT ON;
    SELECT ProductID, ProductName, Price
    FROM dbo.Products
    WHERE Price <= @MaxPrice
    ORDER BY ProductID;
END;
GO

EXEC dbo.GetProductsUnder @MaxPrice = 0.40;
GO

Calling EXEC dbo.GetProductsUnder; with no argument applies the default of 1.00 and returns all four rows. To edit the procedure later, run the same statement as CREATE OR ALTER PROCEDURE, which creates it if it is missing and replaces the body if it isn't, keeping the permissions granted on it.

For the table this procedure reads, see SQL Server: How to create a table.

Stored procedures in DbSchema

Design and visualize
your database schema

Edit referenced records
in related tables

Query your data
visually too

Reuse the SQL
generated

Free Download

Connect DbSchema to SQL Server through the SQL Server JDBC driver and it reverse-engineers the schema, procedures included. Procedures are one of the few things JDBC doesn't expose on its own, so DbSchema reads them with its own queries per database, which you can inspect and adjust in the database settings. They then appear in the Project Structure panel on the left, beside the tables, views and indexes, as described on the interface page.

To create one, paste the CREATE PROCEDURE statement into the DbSchema SQL Editor and click Run Script, which executes the entire editor content rather than a single statement at the cursor. That writes the procedure to the live database, and the SQL History pane keeps the text of what you ran. The design model is a separate file: Schema → Refresh Schema from Database pulls the new procedure into the model, and saving the model writes it into the .dbs file you keep with your code.

Put the query in a procedure once, give the callers EXECUTE on it instead of access to the tables, and write SET NOCOUNT ON at the top so the result set is the only thing coming back. Download DbSchema at https://dbschema.com/download.html and connect it to your SQL Server database to run the statements above and browse the procedures already there: connecting, reverse engineering, the diagrams and the SQL Editor are in the free Community Edition, while saving the model to a .dbs file and schema synchronization are in Pro.

Sources

1. SQL Server documentation: CREATE PROCEDURE (Transact-SQL)
2. SQL Server documentation: Stored Procedures (Database Engine)
3. SQL Server documentation: SET NOCOUNT (Transact-SQL)
4. DbSchema documentation: SQL Editor