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

Publish on DbSchema Blog >>>

Table of Contents

1. Introduction
2. Prerequisites
3. Stored Procedure
   • What is a Stored Procedure
   • Usage of Stored Procedure
   • Advantages of Stored Procedure
   • Limitations of Stored Procedure
   • Restrictions on Using Stored Procedure
   • Permissions Required for Creating a 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

Introduction

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.

Prerequisites

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.

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

CREATE PROCEDURE ProcedureName
AS
BEGIN
   SET NOCOUNT ON;
   SQLStatement
END;
GO

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.

CREATE PROCEDURE GetAllProducts
AS
BEGIN
   SET NOCOUNT ON;
   SELECT * FROM Products;
END;
GO

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:

   CREATE PROCEDURE GetAllProducts
   AS
   BEGIN
      SET NOCOUNT ON;
      SELECT * FROM Products;
   END;
   

   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.

Conclusion

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.

References

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