Guidelines to Create and Execute Stored Procedure: SQL

October 10, 2014 , , , 0 Comments

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’]

  • 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:

Altering Stored Procedure

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


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

SELECT DepartmentID, Name FROM HumanResources.Department
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:


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


Rhett Butler

Some say he’s half man half fish, others say he’s more of a seventy/thirty split. Either way he’s a fishy bastard. Google