Stored Procedures

Next Post
By Admin at 12 Mar 2010, 15:31 PM
  • 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

    1. CREATEPROCEDURE procedure_name
    2. @param data_type = default_value,
    3. @param data_type = default_value,
    4. @param data_type = default_value
    5. AS
    6. -- 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

    1. CREATEPROCEDURE CompanyList
    2. AS
    3. SELECT CompanyName,ContactName,ContactTitle,Phone
    4. FROM CUSTOMERS
    5. RETURN;
    6. 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

    1. Exec CompanyList

    Result

    undefined

    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

    1. ALTERPROCEDURE procedure_name
    2. @param data_type = default_value,
    3. @param data_type = default_value,
    4. @param data_type = default_value
    5. AS
    6. -- statements for procedure here
    7. ALTERkey word is the only change.

    Example

    1. CREATEPROCEDURE CompanyList
    2. AS
    3. SELECT CompanyName,ContactName,ContactTitle,Phone
    4. FROM CUSTOMERS ORDERBY ContactName
    5. RETURN;
    6. 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

    1. ALTERPROCEDURE CompanyList
    2. AS
    3. SELECT CompanyName,ContactName,ContactTitle,Phone
    4. FROM CUSTOMERS ORDERBY ContactName
    5. RETURN;
    6. GO

    After executing the above procedure you will get the following result.

    undefined

    Now all the contact Names are in Ascending Order. You can remove the procedure by using Drop keyword

    Syntax

    1. Drop Proc <procedurename=""><br></procedure>

    Example

    1. 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

    1. CREATE PROC prcGetOrdersByCustomerId
    2. @CustomerId NVARCHAR(5)
    3. AS
    4. SELECT * FROM ORDERS
    5. WHERE CustomerId=@CustomerId
    6. 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.

    1. 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’

    undefined

    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

    1. CREATEPROCEDURE GetCompanyName
    2. @CustomerID VARCHAR(5),
    3. @CompanyName VARCHAR(40) OUTPUT
    4. AS
    5. SELECT @CompanyName=CompanyName FROM Customers
    6. WHERE CustomerID=@CustomerID
    7. GO

    Execution

    1. DECLARE @CompanyName VARCHAR(40);
    2. EXEC GetCompanyName 'ALFKI',@CompanyName OUTPUT;
    3. 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

    1. CREATE PROC GetBit
    2. @Value VARCHAR(1)
    3. AS
    4. if @Value = 'A'
    5. RETURN 1
    6. ELSE
    7. RETURN 0
    8. GO

    The Calling Batch

    1. DECLARE @ReturnValue VARCHAR(5)
    2. EXEC @ReturnValue = GetBit 'A'
    3. PRINT @ReturnValue
    4. EXEC @ReturnValue = GetBit 'B'
    5. 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.


    Comments

     

    Post a comment

    Please correct the following: