Tutorial on Stored Procedures in SQL Servers

104 10
    • 1). Create a stored procedure by using the CREATE PROCEDURE procedurename syntax. Enter the following statements to create a stored procedure called sporderscopy that copies the content of the Orders table into OrdersCopy table.

      CREATE PROCEDURE sporderscopy

      AS

      SELECT * into OrdersCopy

      FROM Orders

      The above procedure will create a copy of the orders table. You can also add parameters, recompile and add encryption with the create procedure statement. The parameters help you to accept values in the procedure that are required for the desired result. The output parameters are used to store values that are passed back to another program. Optional parameters have a default value and do not need an input value. The recompile option makes the system compile the procedure every time it is executed. The encryption option enables the end-user to get the end result without viewing the code.

    • 2). Stored procedures can accept input values and pass back output values to the program calling the stored procedures by declaring the parameters. The input parameters can be optional, if you assign a default value to them while creating the procedure. The output parameters are defined by specifying the keyword OUTPUT.

      CREATE PROCEDURE sporderscount

      @total_order money OUTPUT,

      @order_date smalldatetime = NULL,

      @customer_var varchar(40) = '%'

      AS

      SELECT @total_order = SUM(total_order)

      FROM orders JOIN customers

      ON orders.orderID = customers.orderID

      WHERE (order_date >= @date_var)

      AND (CustomerName like @customer_var)

      The above procedure has two input parameters and returns one output parameter. The output parameter is identified by the OUTPUT keyword.

    • 3). Execute or run a procedure by using the EXEC procedurename syntax. Enter the following SQL statements to execute the stored procedure:

      DECLARE @mytotalorder money

      EXEC spordercount @mytotalorder = @total_order output, '2010-03-21','D%'

      The declare statement creates an output variable that will store the value of the output parameter in the stored procedure. The execute statement is used to run the stored procedure. Since both the input parameters have default values, they need not be assigned a value while executing the procedure.

    • 4). Return values from stored procedures by adding the following statement:

      RETURN @order_count

      The above statement in a stored procedure will return a value and store it in a variable in the program calling the procedure. You can either use a return parameter or the output parameter to pass back a value to the calling program. In the above statement, the procedure will return the number of rows in the orders table.

    • 5). Use the ALTER PROCEDURE procedurename command to modify the stored procedure. The syntax is similar to the create procedure. Enter the following statements to alter the procedure spordercount to add another variable state. The stored procedure will restrict the rows based on the state of residence rather than the name.

      ALTER PROCEDURE sporderscount

      @order_state varchar(30) = NULL,

      @total_order money OUTPUT,

      @order_date smalldatetime = NULL

      AS

      SELECT @total_order = sum(total_order)

      FROM orders JOIN customers

      ON orders.orderID = customers.orderID

      WHERE (order_date >= @date_var)

      AND (CustomerState = @order_state)

    • 6). Delete the stored procedure by using the DROP PROCEDURE procedurename command. You cannot undo the effect of the drop command. Delete the spordercount stored procedure by entering the following statement:

      DROP PROCEDURE sporderscount

Subscribe to our newsletter
Sign up here to get the latest news, updates and special offers delivered directly to your inbox.
You can unsubscribe at any time

Leave A Reply

Your email address will not be published.