MySQL Forums
Forum List  »  Microsoft SQL Server

Stored Procedures
Posted by: cools4u cools4u
Date: January 11, 2011 01:02AM

Stored Procedures :

Stored procedures are extremely useful objects. Not only do they store T/SQL scripts for later execution, but they also provide us with an extremely important security barrier between the user interface and the database. The security barrier is used to prevent the users from needing SELECT, INSERT, UPDATE and/or DELETE rights directly to the database tables and views.

This is done through what is called permissions chaining. When a user has rights to execute a stored procedure they are given temporary rights to use the table objects within the procedures which are used by the table.

Creating stored procedures is very easy. Take your Transact SQL code and put it below the CREATE PROCEDURE command, and end the batch. Like all other database objects the name of the stored procedure must be unique within the schema (or owner for SQL 2000 and below). As an example lets create a stored procedure which returns the names of all the tables in the current database.

CREATE PROCEDURE ShowTables AS
SELECT schema_name(schema_id), name
FROM sys.tables
GO

As you can see the basic syntax is very simple. To run this stored procedure we simply run the stored procedure name.

exec ShowTables

In this way we can create and execute stored procedures...

Car Hire Los Angeles | Car Rental Sanford

Options: ReplyQuote


Subject
Written By
Posted
Stored Procedures
January 11, 2011 01:02AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.