Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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
-------------------------------------------------------------------------------------------------------
Posted 24-Sep-12 21:12pm
Edited 24-Sep-12 21:15pm
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
  Permalink  

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

  Print Answers RSS
0 OriginalGriff 230
1 PIEBALDconsult 150
2 DamithSL 125
3 BillWoodruff 108
4 Garth J Lancaster 90
0 OriginalGriff 5,790
1 DamithSL 4,601
2 Maciej Los 4,012
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,195


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 6 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100