STORED PROCEDURES
| Previous | Home | Next |
SQL server is based on Client/Server technology. A number of clients send queries to the central server. After receiving the query, the server parses the query and check the syntaxes errors. After this processes the request.
The query passes through the network , its add the network congestion and increased the traffic.
A stored procedure is a solution to these problems. It can be created through the Enterprise Manager or using the Query Analyzer window with the CREATE PROCEDURE statement.A Stored Procedure is a precompiled object stored in the database.
Benefits Of Stored Procedure
- Improved performance: SQL server does not have to compile the procedure repeatedly
- Reduction in network congestion: Application need not submit multiple T-SQL statement to the server for the purpose of processing
- Better Consistency: The coding logic and T-SQL statements defined in the procedure are uniformly implemented across all applications as the procedure serves as a single point of control
- Better security mechanism: Users can be granted permission to execute a stored procedure even if they do not own the procedure
Types Of Procedures
- User Defined Stored procedure: The user defined stored procedures are created by users and stored in the current database
- System Stored Procedure: The system stored procedure have names prefixed with sp_. Its manage SQL Server through administrative tasks. Which databases store system stored procedures are master and msdb database
- Temporary Stored procedures: The temporary stored procedures have names prefixed with the # symbol. Temporary stored procedures stored in the tempdb databases. These procedures are automatically dropped when the connection terminates between client and server
- Remote Stored Procedures: The remote stored procedures are procedures that are created and stored in databases on remote servers. These remote procedures can be accessed from various servers, provided the users have the appropriate permission
- Extended Stored Procedures: These are Dynamic-link libraries (DLL's) that are executed outside the SQL Server environment. They are identified by the prefix xp_
| Previous | Home | Next |