Page 77 - DCAP508_DATABASE_ADMINISTRATION

Unit 5: SQL Server Databases

procedure does not need to be reparsed and reoptimized with each use resulting in much Notes
faster execution times.

They can reduce network traffic: An operation requiring hundreds of lines of Transact-
SQL code can be performed through a single statement that executes the code in a procedure,
rather than by sending hundreds of lines of code over the network.

They can be used as a security mechanism: Users can be granted permission to execute a
stored procedure even if they do not have permission to execute the procedure’s statements
directly.
A SQL Server stored procedure is created with the Transact-SQL CREATE PROCEDURE statement
and can be modified with the ALTER PROCEDURE statement. The stored procedure definition
contains two primary components: the specification of the procedure name and its parameters,
and the body of the procedure, which contains Transact-SQL statements that perform the
procedure’s operations.

Prerequisites for Creating Stored Procedure

You can create stored procedures using the CREATE PROCEDURE Transact-SQL statement.
Before creating a stored procedure, consider that:

CREATE PROCEDURE statements cannot be combined with other SQL statements in a
single batch.

Permission to create stored procedures defaults to the database owner, who can transfer it
to other users.
Stored procedures are database objects, and their names must follow the rules for identifiers.

You can create a stored procedure only in the current database.
When creating a stored procedure, you should specify:
Any input parameters and output parameters to the calling procedure or batch.
The programming statements that perform operations in the database, including calling
other procedures.
The status value returned to the calling procedure or batch to indicate success or failure
(and the reason for failure).

Example: Let us explain how to create and use Stored Procedures with Input Parameters
and output parameters as below.
Creating Stored Procedure with Input Parameters
Input Parameters in Stored Procedures are considered as placeholders for data that the user
requires to send. Technically, input parameters are memory variables since they are accumulated
in memory.
For creating Stored Procedure with Input Parameters, code is written as below.

CREATE PROCEDURE Show_Customer
@City varchar(50)
AS

SELECT FirstName, LastName FROM Customer

LOVELY PROFESSIONAL UNIVERSITY 71