DbSchema | SQL Server - How to Create a Stored Procedure?
Table of Contents
- Introduction
- Prerequisites
- Stored Procedure
- SET NOCOUNT ON in Stored Procedure
- Types of Stored Procedures
- Creating a Stored Procedure in sqlcmd
- Creating a Stored Procedure in DbSchema
- Conclusion
- 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
- Basic knowledge of
SQL Server
.
sqlcmd
utility installed and DbSchema
tool.
- 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:
- Managing complex logic: Encapsulate complex SQL logic into a stored procedure, making it easy to
manage
.
- Security: They provide
security
to the data as only necessary data can be shown to the user.
- 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
Code re-usability
and modular
programming.
- 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.
- Security: Execution
permissions
can be granted on Stored Procedures independently of the underlying tables.
Limitations of Stored Procedure
- Complex to write and debug: Writing a stored procedure can become
complex
and cumbersome
as its complexity increases.
- Database dependent: Stored Procedures are written in a
language specific
to the database. Therefore, portability
issues may arise.
- 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:
Open
the command prompt.
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.
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:
Launch DbSchema: Open the DbSchema application on your system.
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.
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.
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.
Save the Procedure: Click on the ‘Save’ button to save the Stored Procedure.
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
- Microsoft Docs: Create a Stored Procedure
- DbSchema Documentation