Quality Testing

Quality is delighting customers

Hi All,

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

-Menakshi


Views: 90

Reply to This

Replies to This Discussion

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
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
Please see the attachment ..hopes it will help you
Attachments:

RSS

TTWT Magazine


Advertisement

Advertisement

Advertisement

Advertisement

© 2019   Created by Quality Testing.   Powered by

Badges  |  Report an Issue  |  Terms of Service