Click here to Skip to main content
15,040,212 members
Please Sign up or sign in to vote.
3.50/5 (3 votes)
15. What is stored procedure? Advantages of SP?
----------------------------------------------------------------------------------------
 A: Stored procedures are set of Structured Query Language (SQL) statements that perform particular task.

• SP have repeatedly using data. It helps to reuse the code.
• SP is reduces the complexity of code in code behind.
• SP increase the security to application, it protect from Sql injection and hacking.
• Code maintenance and changes are done very easily. Instead of changing the code in code behind if changes required.
Syntax of Stored procedure :
SQL
Create Proc Proc_Name
//declaring variables 
@variable_name  data_type =value
As 
Begin
//Body of SP.
End

Note:.
1)To alter the procedure u have to use “Alter”
keyword with replace of “Create”
2) to view stored procedure already in DB
Ex:
sp_helptext SP_Login

sp_login is stored procedure name

out put :
when u run the above code in query editor
u ll see the below output


To Execute the SP:

Exec Proc_Name Parameters
-------------------------------------------------------------------------------------------------------
Posted
Updated 24-Sep-12 20:15pm
v2

Stored Procedure is a set of T-SQL statements in which multiple queries are written to get the output.
Stored procedure have two types of parameters.
a) Output Type Parameter
b) Input Type Parameter

SP may return one or more values through parameters or may not return at all.

SP can return multiple values (max 1024).

We can use insert,delete,update and create statements in SP.

We can use try catch statements in stored procedures
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900