What is a Stored Procedure?

 SQL Stored Procedures for SQL Server

 Stored Procedures:  It is a database object which contains set of per-compiled query.

Types of stored procedure:

               1. System Store Procedure.

               2. User Defined Store Procedure. 

Syntax: 

 Create procedure procedure-name
 As
 SQL Statement
 Go 

Differences between Stored Procedure and User Defined Function in SQL Server:

Stored Procedure:

->It is compiled format.
->Store Procedure may or not return value
->Store Procedure allows select as well as DML(Insert, Update and Delete) statement.
->Try and catch use
->Return 0 or null.

Function: 

-> It will compiled at runtime.
-> Function  must be return a value.
-> Function  allows only select Statement in it.
-> Try and catch statement are not used in function.
-> Return 1 value.

                                    ---------- Work To Do-------------------

1. Store procedure Example.
2. Store procedre with sinle parameter.
3. Store procedure with multiple parameter,changing the parameter order.
4. Alter withe store procedure.
5. Seeing the text of SP. SP_helptext.
6. Drop with store procedure.
    Microsoft uses Sp_prefix for system Store procedure.
7. Using with Encription in store procedure

                           Store procedure Example 👇

-> first create a table  (Test_EmployeeTbl)

 Insert Data in the table 

 Create a Store procedure👇

Create procedure SpGetEmployees
As
Begin
Select Name, Gender from Test_EmployeeTbl
End 

How to show the values of storeprocedure 👇

Simple procedurename

Example:    SpGetEmployees

     OR       exec SpGetEmployees    (Exec means execute)

Store Procedure with single parameter : Next page 👉 Click here