STORED PROCEDURE

What is Stored procedure? 

A stored procedure is a set of precompiled  Structured Query Language (SQL) statements that you assign a name  and  stored on the database server.

Advantages:

1) High performance:

Because stored procedures are already compiled &store on the database server when we call the stored procedure database server will directly execute it ,so stored procedure will give better performance.

2) Less N/W band width consumption:

When we call the stored procedure only the name of stored procedure will send over N/W so it will consume less N/W bandwidth.

3) Reusability:

Once we create stored procedure with some functionality we can call that stored procedure with some functionality we can call the stored procedure from any application i.e stored procedures are reusable.

To work with stored procedure we have two steps:

1) Creating the stored procedure

2) Executing the stored procedure.

Differences between Userdefined stored procedure and System defined SP

System defined SP	User defined SP
1) System defined sp are available in master DB. These sp’s can be directly called from any DB	1) Once we create User defined SP in one database i.e. available to only that database directly. i.e., we can’t call it from some other DB’s directly
2)SDSP will be used for managing sql server	2)UDSP will be used to fulfill the user requirements

Example:

Create Procedure studentdetails

As

Begin

Select *

From students

End

Press F5

Executing stored procedure

Syntax:

Exec <procedurename><parameters>

Example:

Exec studentdetails

Example for update stored procedure

create procedure studentupdate

as

Begin

Update students

Set location='pune'

Where sid=10

End

Executing sotred procedure

Exec studentupdate

Test the table

select *

from students

Example for inserting recording to table

Create procedure insertstudents

as

Begin

Insert into students values(40,'john','Hyderabad')

End

Executing stored procedure

Exec insertstudents

select *

from students

Constraint

• A constraint is a property assigned to a column or the set of columns in a table that prevents certain types of inconsistent data values from being placed in the column(s).
• Constraints are used to enforce the data integrity. This ensures the accuracy and reliability of the data in the database. The following categories of the data integrity exist: