DbSchema Database Designer

DbSchema | SQL Server - How to Create a Stored Procedure?

Publish on DbSchema Blog >>>

SQL Server alt >

Table of Contents

  1. Introduction
  2. Prerequisites
  3. Stored Procedure
  4. SET NOCOUNT ON in Stored Procedure
  5. Types of Stored Procedures
  6. Creating a Stored Procedure in sqlcmd
  7. Creating a Stored Procedure in DbSchema
  8. Conclusion
  9. References


This article covers an in-depth explanation of SQL Server stored procedures, how to create them in the sqlcmd utility and DbSchema, their advantages, limitations, usage, and more. Understanding these concepts is crucial in effectively leveraging SQL Server for database operations.


  1. Basic knowledge of SQL Server.
  2. sqlcmd utility installed and DbSchema tool.
  3. SQL Server Management Studio (SSMS) installed.

For installation and establishing connection you can read our article SQL Server-How to create a database?

Stored Procedure

What is a Stored Procedure

A Stored Procedure is a precompiled set of SQL statements stored in a database. It encapsulates a sequence of operations or queries to execute on a database server.

Usage of Stored Procedure

Stored Procedures are widely used for:

  1. Managing complex logic: Encapsulate complex SQL logic into a stored procedure, making it easy to manage.
  2. Security: They provide security to the data as only necessary data can be shown to the user.
  3. Enhancing performance: They are precompiled and stored on the database. This means that the query processor doesn’t need to create a new plan every time it’s run, improving execution speed.

Advantages of Stored Procedure

  1. Code re-usability and modular programming.
  2. Enhanced performance: SQL Server compiles each Stored Procedure once and then re-utilizes the execution plan. This results in tremendous performance boosts when Stored Procedures are called repeatedly.
  3. Security: Execution permissions can be granted on Stored Procedures independently of the underlying tables.

Limitations of Stored Procedure

  1. Complex to write and debug: Writing a stored procedure can become complex and cumbersome as its complexity increases.
  2. Database dependent: Stored Procedures are written in a language specific to the database. Therefore, portability issues may arise.
  3. Can lead to network congestion: When a large amount of information is involved, network congestion can occur because the information is transferred to the client application from the server.

Restrictions on Using Stored Procedure

  • Stored Procedures can’t be used to create tables or views on different databases or on different servers.

  • Text and image parameters can’t be used as OUTPUT parameters, unless the procedure is a CLR procedure.

Permissions Required for Creating a Stored Procedure

To create a Stored Procedure, one needs to have CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.

SET NOCOUNT ON in Stored Procedure

The SET NOCOUNT ON statement is used within Stored Procedures to stop the message indicating the number of rows affected by a SQL statement. This is done mainly to prevent extra network traffic that would take place to send these messages to the client, improving overall performance for Stored Procedures.

Types of Stored Procedures

Stored Procedures are mainly of two types:

Stored Procedure Type _Description_
User-Defined Stored Procedure These are created by users as per their requirement. They are not a part of the SQL Server.
System Stored Procedure These are installed with SQL Server and start with sp_. They are physically stored in the internal database but logically appear in every database.

Creating a Stored Procedure in sqlcmd

Here’s how you can create a stored procedure using sqlcmd:

  1. Open the command prompt.
  2. Connect to the SQL Server instance using the sqlcmd utility.
sqlcmd -S ServerName -d DatabaseName -U UserName -P Password

Replace ServerName, DatabaseName, UserName, and Password with your actual server name, database name, username, and password respectively.

  1. Create a Stored Procedure. The following is the syntax for creating a simple Stored Procedure:

Let’s say we have a SampleDB database with a Products table. Let’s create a Stored Procedure to retrieve all records from the Products table.

   SELECT * FROM Products;

To know more about creating a table you can read our article SQL Server-How to create a table?

Sample Database:

Products Table:

ProductID ProductName Price
1 Apple 0.5
2 Banana 0.3
3 Cherry 0.2
4 Dates 1.0

Results from the Query:

Following result will be obtained by executing the above query on the sample database:

ProductID ProductName Price
1 Apple 0.5
2 Banana 0.3
3 Cherry 0.2
4 Dates 1.0

This output indicates that the stored procedure GetAllProducts has successfully retrieved all the products from the Products table.

Creating a Stored Procedure in DbSchema

Creating a Stored Procedure in DbSchema involves the following steps:

Below are the detailed steps to create a Stored Procedure using the DbSchema tool:

  1. Launch DbSchema: Open the DbSchema application on your system.

  2. Connect to SQL Server: Click on Connect to establish a connection with your SQL Server database. You need to provide your server name, port number, database name, user name, and password to connect.

  3. Create Procedure: Navigate to the ‘Database’ menu and click on ‘Create new’ > ‘Procedure’. A new window will open where you can write your Stored Procedure.

  4. Enter Stored Procedure Details: In the window that opens, you need to provide the name of the Stored Procedure and the SQL script for the Stored Procedure. Here is an example:

       SELECT * FROM Products;

    This Stored Procedure, named GetAllProducts, retrieves all the records from the Products table in your database.

  5. Save the Procedure: Click on the ‘Save’ button to save the Stored Procedure.

  6. Execute the Procedure: Once the Stored Procedure is saved, you can execute it by right-clicking on the procedure name and selecting ‘Execute’. You can then view the results in the ‘Output’ window.

Visually Manage SQL Server using DbSchema

DbSchema is a SQL Server client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.

Key Features of DbSchema:

Following are the key features of DbSchema which distinguish it from other database GUI tools.


Stored Procedures offer a robust and efficient way to interact with a database in SQL Server. By encapsulating logic in a single entity, they enhance code re-usability, security, and performance. While they have certain limitations and restrictions, understanding these and knowing how to handle them can allow for optimal usage of Stored Procedures.


  1. Microsoft Docs: Create a Stored Procedure
  2. DbSchema Documentation

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.

Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.

Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.

Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.

SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.

Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.

Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.

Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.

DbSchema can be downloaded for free. No registration is required.