Click here to Skip to main content
12,634,651 members (23,973 online)
Rate this:
Please Sign up or sign in to vote.
See more: SQL-server-2005 SQL-Server , +
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
//Body of SP.

1)To alter the procedure u have to use “Alter”
keyword with replace of “Create”
2) to view stored procedure already in DB
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
Updated 24-Sep-12 21:15pm
Rate this: bad
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

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

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.161208.2 | Last Updated 6 Feb 2013
Copyright © CodeProject, 1999-2016
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