What is stored procedure in sql server with example?

Hi guys,

 This blog we discuss about stored procedure and  how to create it using sql server.

Stored procedure:-

Stored procedure are precompiled Transact –SQL(T-SQL) statements stored in a sql servr database. It usually provide the best performance of any type of query.

Advantage of stored procedure:-

1-    Compilation step is required only once when the stored procedure is created.

2-     It helps in re usability of sql code.

3-    Stored Procedure can reduce network traffic. 

4-    Stored procedures provide better security to your data.

5-    Stored procedure set up to run automatically when sql server starts up.

6-    Stored procedures are explicitly  invoked.

Types of  stored procedure:

Basically two types of 3 types procedure available in sql server

1-    System defined stored procedure.

2-    User defined stored procedure.

3-    Extended stored procedure.

System defined stored procedure:

System defined stored procedure stored in the master database and these  are starts with  a prefix _sp. These procedure s can be used to perform variety of tasks like show all data base  in sqlserver, rename database etc.

    Example: - sp_helpdb, sp_rename , sp_help etc

    User defined stored procedure:-

 User Defined Stored procedure: The user defined stored procedures are created by       users and stored in the current database.

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_