Search

Friday, October 10, 2014
0 comments

Guidelines to Create and Execute Stored Procedure: SQL

9:45 PMFriday, October 10, 2014
To create stored procedure database developer have to keep some points in mind. The following points need to be considered before creating a stored procedure:

  • You cannot combine the CREATE PROCEDURE statement with other SQL statements in a single batch.
  • You must have the CREATE PROCEDURE permission to create a procedure in the database and the ALTER permission on the schema, where the procedure is being created.
  • You can create a stored procedure only in the current database.
  • After creating a stored procedure, you can execute the procedure. You can also alter the procedure definition or drop it, if not required.

Executing a Stored Procedure

A procedure can be executed by using the EXEC PROCEDURE statement. The syntax of the EXEC PROCEDURE statement is:

EXEC | EXECUTE PROCEDURE proc_name AS [ { LOGIN|USER} = ‘name’]
Where,

  • Proc_name, is the name of the procedure you need to execute.
  • LOGIN|USER specifies the name of the login of another user on the same database server or another user in the same database that you need to impersonate. While executing a procedure you can impersonate the rights of another user or ligin to execute the procedure. This allows a user who does not have permission to execute a procedure to execute it using the permissions assigned to other users.

Consider an example. Robert has created a stored procedure named DispEmpDetail that displays the details of the employees. Kim needs to execute the procedure but does not have the execution rights. In such a case, Kim can use the rights of Robert to execute. For this, Kim needs to impersonate Robert.

You can execute the stored procedure, prcDept, as shown in the following statement:
EXEC PROCEDURE prcDept

Altering Stored Procedure

A stored procedure can be modified by using the ALTER PROCEDURE statement. The syntax of the ALTER PROCEDURE statement is:

ALTER PROCEDURE proc_name

You can alter the stored procedure, prcDept, as followis:

ALTER PROCEDURE prcDept
AS
BEGIN
SELECT DepartmentID, Name FROM HumanResources.Department
END
In the preceding code, the DepartmentID attribute will be displayed along with the department name.

Dropping a Stored Procedure

You can drop a stored procedure from the database by using the DROP PROCEDURE statement. The syntax of the DROP PROCEDURE statement is:

DROP PROCEDURE proc_name

You cannot retrieve a procedure once it is dropped. You can drop the prcDept stored procedure by using the following statement:

DROP PROCEDURE prcDept

Protected by Copyscape Online Copyright Protection Software

0 comments :

Post a Comment

 
Toggle Footer
Top