A Stored Procedure is pre-compiled collection of SQL statements and SQL command logic in stored in database.
The main purpose of stored procedure is to hide direct SQL queries from the code and improve performance of database operations such as SELECT, UPDATE, and DELETE.
Stored Procedures can be cached and used too.
We can create a Stored Procedure using the Create proc statement
Use of SET NOCOUNTON in Stored Procedure
SET NOCOUNT ON is a set statement which prevents the message which shows the number of rows affected by T-SQL query statements
NOCOUNT means do not count that is ON.
This means when SET NOCOUNT ON is there then there is no message that will show number of rows affected.
When SET NOCOUNT ON
When SET NOCOUNT OFF
Types of Stored Procedure
There are two types of Stored Procedures in SQL Server
- User Defined stored procedures
- System stored procedures
User Defined Stored Procedures
User defined stored procedures are create database developers or database administrators.
These store procedures contain one or more SQL statement to SELECT, UPDATE, or DELETE records from database tables.
User defined stored procedures can take input parameters and return output parameters.
User defined stored procedures is a mixture of DDL(Data Definition Language) and DML(Data Manipulation Language) commands.
User defines stored procedures are further classified into two types:
- T-SQL Stored Procedures
- CLR Stored Procedures
System Stored Procedures
System Stored Procedures are useful in performing administrative and informational activities in SQL Server. Most commonly used system Stored Procedures are described below.
To Find All Database Object Information
To Find Table Information
exec sp_help 'user'
To find list of all tables in database
To get detailed information about tables in database
SELECT * FROM sys.tables
To get definition of specific procedure “SelectUserDetail” in the master database.
exec sp_helptext SelectAllCustomers
To get the dependent object details.
exec sp_depends Registration
Comments in Stored Procedures
We can add comments in stored procedure in following ways:
- Single line comment
Use --(two hyphens/ dash) for single line of comment
- Multiline comment
Start with /* …. End with */Create SELECT Store Procedure
Click on your database and expand “Programmability” and right click on “Stored Procedures” or press CTRL+N to get new query window.
You can write the SELECT query in between BEGIN and END to get select records from the table.
Execute Stored Procedure in SQL Server
To run stored procedure in SQL server run below commands
- EXEC <store procedure name>
Another way to is to right click on Stored Procedure name and select “Execute Stored Procedure”
Parameters in Stored Procedures
Parameters are used to pass input values and return output values in store procedures.
- Input parameters: Pass value to a stored procedure
- Output Parameters: Return values from stored procedure
INSERT query in stored procedure
UPDATE query in stored procedure
DELETE query in stored procedure