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:
EXEC | EXECUTE PROCEDURE proc_name AS [ { LOGIN|USER} = ‘name’]
Where,
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
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.
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
- 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