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 :
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
-------------------------------------------------------------------------------------------------------