A stored procedure is a subroutine available to applications accessing a relational database system. Stored procedures (sometimes called a sproc, StoPro, or SP) are actually stored in the database data dictionary.
Typical uses for stored procedures include data validation (integrated into the database) or access control mechanisms. Furthermore, stored procedures are used to consolidate and centralize logic that was originally implemented in applications. Large or complex processing that might require the execution of several SQL statements is moved into stored procedures and all applications call the procedures only.
Stored procedures are similar to user-defined functions (UDFs). The major difference is that UDFs can be used like any other expression within SQL statements, whereas stored procedures must be invoked using the CALL statement[citation needed]
Advantages of Stored Procedure
Reduced network usage between clients and servers
Enhanced hardware and software capabilities
Improved security
Reduced development cost and increased reliability
Centralized security, administration, and maintenance for common routines
You can create stored procedures using the CREATE PROCEDURE Transact-SQL statement.
To execute a stored procedure in all databases of SQL Server, you should create the candidate stored procedure in the master database and pass it as a parameter to the attached stored procedure.


