A stored procedure is a group of Transact-Sql statements compiled into a single execution plan. Stored procedures offer huge benefits
-
We can Manage, control, and validate data
-
They can be used for access mechanisms
-
We can avoid large queries
-
They reduce network traffic since they need not be recompiled
-
Even though the stored procedure itself may be a complex piece of code, we need not write it over and over again. Hence stored procedures increases reusability of code
-
We can set the permissions for stored procedures, hence implementing security
Create, Alter and Drop Stored Procedures
Stored procedures are managed by the means of the Data Definition Language (DDL) commands.
The Create command must be the first command in a batch. The end of the batch ends the creation of the stored procedure.
Syntax
CREATE PROCEDURE procedure_name
@param data_type = default_value,
@param data_type = default_value,
@param data_type = default_value
AS
-- statements for procedure here
In the next line to the create statement, we will give a set of parameters along with the data type. While creating the parameter we can set the default values. This default value will be assigned if the calling section is missing this parameter. The keyword “As” should be placed before the procedure statement.
Now let’s see an example for creating a procedure statement
Example
CREATE PROCEDURE CompanyList
AS
SELECT CompanyName,ContactName,ContactTitle,Phone
FROM CUSTOMERS
RETURN;
GO
In the above example, we have created the CompanyList stored procedure. In the statement section we have one simple select statement which displays CompanyName, ContactName, ContactTitle and Phone details for the customers.
Execute the procedure
Execute, or Exec Command, is used to execute the stored procedure
Syntax
Exec or Execute
Example
Exec CompanyList
Result
CompanyList lists all the reocrds from the customers table
Suppose you want to display the customer list with the ascending order of the Contact Name. In that case you have to modify the existing stored procedure CompanyList.
Syntax
ALTER PROCEDURE procedure_name
@param data_type = default_value,
@param data_type = default_value,
@param data_type = default_value
AS
-- statements for procedure here
“ALTER” key word is the only change.
Example
CREATE PROCEDURE CompanyList
AS
SELECT CompanyName,ContactName,ContactTitle,Phone
FROM CUSTOMERS ORDER BY ContactName
RETURN;
GO
When you execute the above procedure it will return the following error.
Msg 2714, Level 16, State 3, Procedure CompanyList, Line 5
There is already an object named 'CompanyList' in the database.
This is because the procedure CompanyList already exists. The correct procedure is
ALTER PROCEDURE CompanyList
AS
SELECT CompanyName,ContactName,ContactTitle,Phone
FROM CUSTOMERS ORDER BY ContactName
RETURN;
GO
After executing the above procedure you will get the following result.
Now all the contact Names are in Ascending Order. You can remove the procedure by using Drop keyword
Syntax
Drop Proc <procedure name=""><br></procedure>
Example
Drop Proc CompanyList
The above command will delete the procedure CompanyList
Passing Data to Stored Procedure
The stored procedure is more useful when it is manipulated by parameters. Sql Server stored procedures may have more input and output parameters (upto 2100 to be exact.)
Input Parameters
We can add parameters which pass data to the stored procedure by listing the parameters after the procedure name in the Create Procedure command. Each parameter begins with the @ sign and valid data type. When this stored procedure is called, all of the parameters must be specified. We can set a parameter which allows default values.
Example
CREATE PROC prcGetOrdersByCustomerId
@CustomerId NVARCHAR(5)
AS
SELECT * FROM ORDERS
WHERE CustomerId=@CustomerId
GO
We have created the stored procedure prcGetOrdersByCustomerId with the parameter @CustomerId and its data type is nvarchar. This procedure will return all of the order information with the specified customer id.
EXECUTE prcGetOrdersByCustomerId 'VINET'
Here we have passed the parameter value ‘VINET’ to the procedure prcGetOrdersByCustomerId. And the procedure will return all of the rows which have the CustomerId value ‘VINET’
System Stored Procedures
Microsoft performs hundreds of tasks with system stored procedures. System stored procedures are stored in the Master Database. These procedures start with sp_ which means that it is a system stored procedure, and it can be executed from any database. If the system finds any name conflict between system and local stored procedures in the local user database, then the system data from the local database will be executed.
Returning Data from Stored Procedures
Sql server provides various ways to returning data from the stored procedure.
Output Parameters
Output parameters allow the procedure to return data from the stored procedure. Output is the keyword which is required both when the procedure is created, and when it is called. The Output parameter will be a local variable in the stored procedure. Output parameters are useful for returning single units of data when a whole record set is not required. For returning a single row of information using output parameters is faster than preparing a record set.
Example
CREATE PROCEDURE GetCompanyName
@CustomerID VARCHAR(5),
@CompanyName VARCHAR(40) OUTPUT
AS
SELECT @CompanyName=CompanyName FROM Customers
WHERE CustomerID=@CustomerID
GO
Execution
DECLARE @CompanyName VARCHAR(40);
EXEC GetCompanyName 'ALFKI',@CompanyName OUTPUT;
PRINT @CompanyName
Output
Alfreds Futterkiste
We have just created the stored procedure called GetCompanyName with two inputs. One of the inputs is CustomerID and the other is CompanyName. CompanyName is declared with the keyword Output. It is now an output parameter. There’s no need to give the input for the variable CompanyName, we just have to pass the value for CustomerID. In the body section we get the companyname from the customers table, and store it in the @CompanyName output variable.
In the execution part, we first declared the output variable @CompanyName, because we are going to store the value in the CompanyName (which is returned from the stored procedure).
While calling the procedure pass the @CustomerID and Ouptut paramter name with the keyword OUTPUT.
When we execute the code, the output “Alfreds Futterkiste” is displayed.
Using Return Command
A return command unconditionally terminates the procedure, and returns a value to the calling batch or client.
Example
CREATE PROC GetBit
@Value VARCHAR(1)
AS
if @Value = 'A'
RETURN 1
ELSE
RETURN 0
GO
The Calling Batch
DECLARE @ReturnValue VARCHAR(5)
EXEC @ReturnValue = GetBit 'A'
PRINT @ReturnValue
EXEC @ReturnValue = GetBit 'B'
PRINT @ReturnValue
Result
1
0
The getBit procedure will accept a varchar value. We passed ‘A’ then ‘B’. If the value is ‘A’ then the procedure will return the value 1, and if the value is ‘B’ then it will return the value 0.