Applying Batches, Stored Procedures and Functions Part – 4
Applying Stored Procedure
Batches that we discussed in the earlier part are impermanent in form. In the direction of applying a batch for more than once, an individual must reconstruct the SQL commands as well as need to put them again in the server for execution. This hint for a bigger headache, as the server necessities to assemble in addition has to form the implementation strategy for these commands over. So, if an individual want to apply a batch numerous times, the individual can keep the SQL commands of the batch in a stored procedure. A stored procedure is a preassembled item kept in the database.
Stored Procedure can call the Data Definition Language (DDL) as well as Data Manipulation Language (DML) commands in addition to giving back the data. If an individual want to allocate data to the variables mentioned in the stored procedures at the execution time, then an individual can permit parameters at the time of implementing them. An individual can apply a stored procedure from a different stored procedure too. This assistances in practicing the functionality of the called stored procedure in the calling stored procedure. As a database designer, it is significant for an individual to know in what manner to apply stored procedures.
Constructing Stored Procedures
An individual can form stored procedure by means of the CREATE PROCEDURE command. The code of the CREATE PROCEDURE command is:
CREATE PROCEDURE My_Procedure_Name
AS
BEGIN
My_SQL_Command
…..
END
here,
· My_Procedure_Name – It stipulates the user defined name of the stored procedure that an individual want to give.
The subsequent instance forms a stored procedure to observe the customer names from the [ Customer ] . [ Details ] tables or relation:
CREATE PROCEDURE SP_Customer_Details
AS
BEGIN
SELECT Name from [ Customer ] . [ Details ]
END
When the CREATE PROCEDURE command is implemented, the server assembles the stored procedure plus keeps it as a database item. The stored procedure is at that time obtainable for numerous systems to apply. The procedure of assembling a stored procedure includes the subsequent steps:
1. The stored procedure is assembled plus its modules are divided into several parts. This procedure is identified as Parsing.
2. The presence of the mentioned items, like as tables or relations as well as views (logical tables) are double-checked beforehand. This procedure is identified as Resolving.
3. The name of the stored procedure is kept in the sysobjects table or relation plus the syntax which has formed the stored procedure is kept in the syscomments table or relation.
4. The stored procedure is assembled as well as a plan for by what method the request will be executed is also formed. This plan is stated as implementation plan. The implementation plan is kept in the stored procedure cache.
5. Whenever the stored procedure is implemented for the first (1st) time, the implementation plan will be read as well as completely optimized and then it will be executed. The succeeding time the stored procedure is implemented in the similar setting, it will be read straight from the cache. This upsurges performance, as there is no repetitive assembling.
Afterward forming the stored procedure, an individual can see the syntax of the stored procedure by means of the sp_helptext command.
Guiding Principle For Constructing Stored Procedures
The subsequent facts must be taken care off in advance of forming a stored procedure:
· An individual cannot associate the CREATE PROCEDURE command with additional SQL commands in a particular batch.
· An individual should have the CREATE PROCEDURE authorization to form a stored procedure in the database as well as the ALTER authorization on the schema, where the stored procedure is begin formed.
· An individual can form a stored procedure solely in the present database.
Afterwards forming a stored procedure, an individual can apply the stored procedure. An individual can modify the stored procedure description or else delete it, if not needed too.
Running a Stored Procedure
A stored procedure can be implemented by means of the EXECUTE PROCEDURE command. The code of the EXECUTE PROCEDURE command is:
EXECUTE | EXEC PROCEDURE My_Procedure_Name AS [ { USER | LOGIN } = ‘ name ’ ]
here,
· My_Procedure_Name – It is the name of the stored procedure that an individual wants to execute.
· USER | LOGIN – It stipulates the name of the login of a new user on the similar database server otherwise alternative user in the similar database which an individual want to imitate. When implementing a stored procedure an individual can imitate the privileges of a different user or else login to execute the stored procedure. This permits a user who does not have authorization to execute a stored procedure to apply it by means of the authorizations allocated to another user.
Think through an instance; Person1 has formed a stored procedure titled SP_Customer_Details which shows the particulars of the customers. Now Person2 wants to execute the stored procedure however does not have the implementation privileges. For this type of situations, Person2 can custom the privileges of Person1 to apply the stored procedure. For this, Person2 must imitate Person1. An individual can execute the stored procedure, SP_Customer_Details as presented in the subsequent commands:
EXECUTE PROCEDURE SP_Customer_Details
Modifying a Stored Procedure
The store procedure can be altered by means of the ALTER PROCEDURE command. The code of the ALTER PROCEDURE command is:
ALTER PROCEDURE My_Procedure_Name
An individual can modify the stored procedure, SP_Customer_Details as shown:
ALTER PROCEDURE SP_Customer_Details
AS
BEGIN
SELECT CustomerID, Name from [ Customer ] . [ Details ]
END
In the previous syntax, the CustomerID column or attribute will be showed along with the customer name (Name).
Deleting a Stored Procedure
An individual can delete a stored procedure from the database when not needed by means of the DROP PROCEDURE command. Once a stored procedure is deleted it cannot be recovered latter, so an individual must be cautious before deleting it. The code of the DROP PROCEDURE command is:
DROP PROCEDURE My_Procedure_Name
An individual can delete the stored procedure, SP_Customer_Details as shown:
DROP PROCEDURE SP_Customer_Details
In the upcoming article we will be discussing Applying Parameters In A Stored Procedure, Giving Back The Data From Stored Procedure and Invoking A Stored Procedure From A Different Stored Procedure.