Need information about Stored Procedure

Hi All,

I need information about Stored Procedure. if you are having any documents / ppts / pdf files, please share me.

-Menakshi

Tags: Procedure, Stored

0 members like this

▶ Reply to This

Replies to This Discussion

Permalink Reply by Sudha Ankireddy on August 6, 2010 at 5:40pm

Hi Menakshi,

I need some information for which programming/scripting language do u need information about stored procedure.

Concept is same in all.

Regards,
Anki

▶ Reply

Permalink Reply by ѕαи∂єєρ on August 6, 2010 at 5:40pm

Writing the first stored procedure

Here is the first stored procedure source code:
view source
print?
1.CREATE PROCEDURE Delete_Employee
2. (@EmployeeId INT)
3.AS
4.
5.BEGIN
6. DELETE FROM Employees
7. WHERE EmployeeId = @EmployeeId;
8.END

A stored procedure must contains at least three parts: stored procedure name, parameter list and its body.

The CREATE PROCEDURE is similar to CREATE TABLE or INDEX statement. It is actually a SQL statement. The CREATE PROCEDURE will force the database server add the stored procedure to the its catalog. The name of stored procedure is followed after the CREATE PROCEDURE statement, in this case it is Delete_Employee. It would be the best that the name is meaningful and follows by the naming convention of the database server specification, for example each stored procedure should begin with "sp". In almost relation database product, the name of stored procedure must be unique.

The second part of the stored procedure is parameter list. In this case the list contains only one parameter @EmployeeId (the employee identity). Microsoft SQL Server requires prefix @ for every parameters and variables of stored procedure. Followed each parameter is its type, in this case, its type is integer (INT).

The main part of a stored procedure is the stored procedure body. It starts with keywords BEGIN and ends with keyword END. In this example the body is very simple; It deletes employee by employee identity.

When all syntax statements inside body are correct, the database server will store the stored procedure name and code in its catalog for reusing later by another stored procedure or programs.
Calling a stored procedure

We can call a stored procedure from the console window, from another stored procedure or from a program which can access database server. The syntax of calling a stored procedure is simple as follows:
view source
print?
1.EXEC spName(parameter_value_list)

The EXEC statement is used to invoke a stored procedure. After the EXEC statement is the stored procedure name followed by parameter list. This is an example to delete anemployees with identity is 8 by calling the sample procedure above Delete_Employee:
view source
print?
1.EXEC Delete_Employee(8)

If a stored procedure has more than one parameters, the values of them can be passed to it and separated by a comma.

As you see writing and calling a stored procedure is very simple and easy. In the following tutorials, we will show you the feature and syntax of a stored procedure along with statement which can be used inside the body so you can empower its power. Next you will learn how to use parameter list to pass and get data in and out of stored procedures parameter list in stored procedure.

go thru with the following link

http://msdn.microsoft.com/en-us/library/aa258259(SQL.80).aspx