Back to all posts

SQL Server Stored Procedure Tutorial with sqlcmd and DbSchema

For SQL Server developers who write T-SQL queries and want one of them kept in the database as a stored procedure.

by Malik Umair Sajjad · 15-Jun-2023 · Updated 29-Oct-2026 · Sql server

On this page

When the reporting screen, a nightly job and a support script each carry their own copy of a query, the copies drift apart: one gets the new filter and the others don't. A stored procedure keeps the query in the database under one name, and every caller runs that one copy:

CREATE PROCEDURE schema_name.procedure_name
    @parameter_name data_type = default_value
AS
BEGIN
    SET NOCOUNT ON;
    sql_statement;
END;
GO

EXEC schema_name.procedure_name @parameter_name = value;

CREATE PROCEDURE must be the only statement in its batch, which is why a GO line closes it before the EXEC.

Create and run a procedure in sqlcmd

Connect the sqlcmd utility to the database that the procedure belongs in. Without -P, sqlcmd asks for the password, which keeps it off the command line; the sqlcmd documentation calls -P insecure.

sqlcmd -S your-server -d SampleDB -U your-login

The examples use one 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 (ProductID, ProductName, Price) 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 whole 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

GO is not T-SQL: sqlcmd reads it as the end of a batch and sends the statements since the previous GO to the server. EXEC is short for EXECUTE, and a call may leave it out only when the call is the first statement of its batch.

Name the schema, as in dbo.GetAllProducts, so that SQL Server searches no other schema and can't run a procedure of the same name from another schema by mistake.

A procedure may name a table that doesn't exist yet: creating it checks only the syntax, and the names are resolved at the first run. A procedure over a misspelled table name is created without complaint and fails when called.

Input parameters and default values

Give a parameter a default, and callers may leave it out:

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;  -- by name
EXEC dbo.GetProductsUnder 0.40;              -- by position
EXEC dbo.GetProductsUnder;                   -- the default, 1.00
GO

The first two calls return the products priced at 0.40 or less, the third every product up to 1.00. Positional values follow the declared order; named ones can come in any order, but once a call names one parameter, it has to name every parameter after it.

A default must be a constant or NULL, so @Since date = GETDATE() isn't allowed; pass the function's value in a variable instead. A parameter stands only where a constant would, never for a table or column name.

OUTPUT parameters and return codes

A procedure hands data back to its caller in three ways: the rows of each SELECT in its body, the values of its OUTPUT parameters, and one integer return code.

The caller passes input parameter values to the procedure; the procedure hands back result sets, OUTPUT parameter values and one integer return code

This procedure counts the products under a price instead of listing them, and uses a return code to report a NULL price:

CREATE PROCEDURE dbo.CountProductsUnder
    @MaxPrice     decimal(10,2),
    @ProductCount int OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    IF @MaxPrice IS NULL
        RETURN 1;
    SELECT @ProductCount = COUNT(*)
    FROM dbo.Products
    WHERE Price <= @MaxPrice;
    RETURN 0;
END;
GO

DECLARE @Count int, @Status int;
EXEC @Status = dbo.CountProductsUnder
    @MaxPrice = 0.40,
    @ProductCount = @Count OUTPUT;
SELECT @Status AS Status, @Count AS ProductCount;
GO

OUTPUT appears twice, in the declaration and in the call. A call that leaves it out gets no error, but can't use the value either. The return code is read with EXEC @Status = ... and is always an int; a RETURN of NULL returns 0 with a warning. Microsoft's system procedures, unless documented otherwise, return 0 for success and anything else for failure, a rule worth copying. Use an OUTPUT parameter for a single value such as a count, a SELECT for rows, and the return code for success or failure.

Change, view or drop a procedure

To change a procedure, run its whole statement again as ALTER PROCEDURE, or as CREATE OR ALTER PROCEDURE, which also creates it when it is missing (from SQL Server 2016 SP1). Either beats dropping and creating it again: ALTER PROCEDURE leaves the permissions alone, while a drop removes the permissions that were granted on the procedure explicitly.

To read the source of a procedure already in the database, ask for its definition:

SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.GetProductsUnder'));
EXEC sp_helptext N'dbo.GetProductsUnder';
GO

DROP PROCEDURE IF EXISTS, from SQL Server 2016, removes one and skips the error when it is already gone:

DROP PROCEDURE IF EXISTS dbo.GetAllProducts;
GO

Look first for the jobs, scripts and procedures that call it: they can fail on their next call.

SET NOCOUNT ON in a stored procedure

After each statement that reads or changes rows, SQL Server sends the application a count message, such as this one from the SET NOCOUNT reference:

(5 rows affected)

SET NOCOUNT ON stops those messages, and the DONEINPROC message sent for each statement in a procedure. @@ROWCOUNT is still updated, so logic that checks the row count keeps working.

The CREATE PROCEDURE best practices make it the first statement of the body and see "no measurable performance benefit" on today's hardware, while the SET NOCOUNT page still says it can give a significant boost to procedures with many statements or loops. Write it for the clean output, and measure before you count on more.

Why put a query in a procedure

The strongest reason is permissions: callers can work with the tables through a procedure without holding permissions on the tables themselves, so a reporting user can be granted the procedure instead of the table:

GRANT EXECUTE ON dbo.GetProductsUnder TO ReportingUser;
GO

Parameter values are treated as literals rather than code, which makes injecting a command harder. The other gains, from Microsoft's stored procedures overview:

Only the EXEC call crosses the network, not every line of the query.
A change to the tables means a change to the procedure, and the application stays as it is.
The plan compiled at the first run can be reused by later runs while it stays in the plan cache.

The plan reuse has a catch: after large changes to the tables or the data, the cached plan can make the procedure slower, and recompiling it to force a new plan can help.

Creating a procedure takes CREATE PROCEDURE permission in the database plus ALTER permission on the schema, or membership in the db_ddladmin fixed database role.

What a procedure can't contain, and other limits

A procedure body rejects these statements, wherever they appear:

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

The limits that a working procedure can hit:

Up to 2,100 parameters.
Up to 32 levels of procedures calling procedures. Going deeper fails the whole chain, and @@NESTLEVEL tells you the current level.

Don't start a name with sp_: that prefix marks the system procedures, and one with the same name can break your application. A name starting with # creates a temporary procedure in tempdb that only its connection sees, until that connection closes; with ##, every connection sees it until the last session using it ends.

Stored procedures in DbSchema

Connect DbSchema to SQL Server and import the schema; connecting and importing only read from the database. To create a procedure there:

Press Editor in the toolbar and choose SQL Editor, or press Ctrl+E.
Paste the CREATE PROCEDURE statement without its GO line, select it, and press Run, which runs the selected text. If the Commit button blinks afterwards, press Commit to keep the change.
Type an EXEC line, put the cursor on it, and press Run. The rows show in the result grid.

The SQL Editor works in every edition of DbSchema, Community included. It writes the procedure to the live database, not to the DbSchema model. Database → Refresh Model from Database brings the change into the model; it is part of schema synchronization, which needs DbSchema Pro.

The DbSchema SQL Editor with Run and Run Script in its toolbar and a result grid under the query; the tree on the left lists Procedures beside Tables and Views for the shop schema

For readers without DbSchema, File → Export As... → HTML5/PDF Documentation writes the schema documentation; tick Functions and Procedures and Procs/Triggers Text to include each procedure's SQL source.

The Schema Documentation dialog, with the Functions and Procedures and Procs/Triggers Text boxes under Content

Download DbSchema, connect it to your SQL Server database, and run the procedures above in the SQL Editor, all in the free Community Edition. Saving the model, schema synchronization and the HTML5 documentation are in DbSchema Pro.