Some basics about Stored Procedure.

Stored Procedure

 A stored procedure is a precompiled group of Transact-SQL statements, and is saved to the database (under the “Stored Procedures” node). Programmers and administrators can execute stored procedures either from the SQL Server Management Studio or from within an application as required.

Stored procedures improve performance by reducing network traffic and CPU load.

Benefits

Precompiled execution: SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.

Modular programming: You can write a stored procedure once, then call it from multiple places in your application.

Performance: Stored procedures provide faster code execution and reduce network traffic.

  • Faster execution: Stored procedures are parsed and optimized as soon as they are created and the stored procedure is stored in memory. This means that it will execute a lot faster than sending many lines of SQL code from your application to the SQL Server. Doing that requires SQL Server to compile and optimize your SQL code every time it runs.
  • Reduced network traffic: If you send many lines of SQL code over the network to your SQL Server, this will impact on network performance. This is especially true if you have hundreds of lines of SQL code and/or you have lots of activity on your application. Running the code on the SQL Server (as a stored procedure) eliminates the need to send this code over the network. The only network traffic will be the parameters supplied and the results of any query.

Enhanced security controls. Users can execute a stored procedure without needing to execute any of the statements directly. Therefore, a stored procedure can provide advanced database functionality for users who wouldn’t normally have access to these tasks, but this functionality is made available in a tightly controlled way.

Structure

Stored procedures are extremely similar to the constructs seen in other programming languages. They accept data in the form of input parameters that are specified at execution time. These input parameters (if implemented) are utilized in the execution of a series of statements that produce some result. This result is returned to the calling environment through the use of a record set, output parameters and a return code.

Creating a Stored Procedure

You create stored procedures in the SQL Server Management Studio using the CREATE PROCEDURE statement, followed by the code that makes up the stored procedure.

The following code creates a stored procedure called “MyStoredProcedure”:

CREATE PROCEDURE MyStoredProcedure

SET ROWCOUNT 10

    SELECT FirstName, LastName,Department
    FROM HumanResources.vEmployeeDepartmentHistory

Once you run this code in the SQL Server Management Studio, the stored procedure is created and appears under the “Stored Procedures” node.

Modifying a Stored Procedure

If you need to modify an existing stored procedure, you simply replace the CREATE with ALTER.

ALTER PROCEDURE MyStoredProcedure AS

Running a Stored Procedure

You can run a stored procedure by using EXECUTE or EXEC. For example, to run the above stored procedure, type the following:

EXEC MyStoredProcedure

 If the stored procedure has spaces in its name, enclose it between double quotes:

EXEC “My Stored Procedure”

 If your stored procedure accepts any parameters, they are placed after the procedure name:

EXEC MyStoredProcedure @ParameterName=”MyParameter”

 Passing Parameters

 Examples in this section demonstrate how to use input and output parameters to pass values to and from a stored procedure.

A. Creating a procedure with input parameters

The following example creates a stored procedure that returns information for a specific employee by passing values for the employee’s first name and last name. This procedure accepts only exact matches for the parameters passed.

IF OBJECT_ID ( ‘HumanResources.uspGetEmployees’, ‘P’ ) IS NOT NULL
    DROP PROCEDURE HumanResources.uspGetEmployees;
GO
CREATE PROCEDURE HumanResources.uspGetEmployees
    @LastName nvarchar(50),
    @FirstName nvarchar(50)
AS
    SET NOCOUNT ON;
    SELECT FirstName, LastName,Department
    FROM HumanResources.vEmployeeDepartmentHistory
    WHERE FirstName = @FirstName AND LastName = @LastName;
GO
The uspGetEmployees procedure can be executed in the following ways:
EXECUTE HumanResources.uspGetEmployees N’Ackerman’, N’Pilar’;
— Or
EXEC HumanResources.uspGetEmployees @LastName = N’Ackerman’, @FirstName = N’Pilar’;
GO
— Or
EXECUTE HumanResources.uspGetEmployees @FirstName = N’Pilar’, @LastName = N’Ackerman’;
GO

B. Using OUTPUT parameters

The following example creates the uspGetList procedure. This procedure returns a list of products that have prices that do not exceed a specified amount. The example shows using multiple SELECT statements and multiple OUTPUT parameters. OUTPUT parameters enable an external procedure, a batch, or more than one Transact-SQL statement to access a value set during the procedure execution.

IF OBJECT_ID ( ‘Production.uspGetList’, ‘P’ ) IS NOT NULL
    DROP PROCEDURE Production.uspGetList;
GO
CREATE PROCEDURE Production.uspGetList @Product varchar(40)
    , @MaxPrice money
    , @ComparePrice money OUTPUT
    , @ListPrice money OUT
AS
    SET NOCOUNT ON;
           SELECT p.[Name] AS Product, p.ListPrice AS ‘List Price’
           FROM Production.Product AS p
          JOIN Production.ProductSubcategory AS s
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
          WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice;

— Populate the output variable @ListPprice.

SET @ListPrice = (SELECT MAX(p.ListPrice)
          FROM Production.Product AS p
          JOIN  Production.ProductSubcategory AS s
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
          WHERE s.[Name] LIKE @Product AND p.ListPrice < @MaxPrice);

— Populate the output variable @compareprice.

SET @ComparePrice = @MaxPrice;
GO
Author: Anju Philip
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s