dcsimg
Results 1 to 2 of 2

Thread: Stored Procedures and parameters

  1. #1
    Rupert Hollom Guest

    Stored Procedures and parameters

    I want to create a stored procedure with SQL Server 6.5 that CAN take 3 parameters, all of which are optional. The declaration for the stored procedure is :
    CREATE PROCEDURE BackOrdersII @CustCode varchar(10), @FromDate datetime, @ToDate datetime AS
    SELECT * FROM ISO_Details
    WHERE DATEDIFF(dd, fldEst_Del_Date, getdate()) > 0 AND CONVERT(char(12),fldActual_Del_Date,3)=`01/01/00` AND CONVERT(char(12),fldEst_Del_Date,3)<>`01/01/00` AND fldDeleted<>1
    ORDER BY DATEDIFF(dd, fldEst_Del_Date, getdate()) DESC

    How do I formulate the procedure to allow me to select from the table, keeping the current WHERE clause but adding extra items to allow the results to be further filtered depending upon which of the parameters are given to the procedure. Any one, two, or all three parameters could be given.

    Sorry if this seems like a simple question but I am only just getting into using stored procedures.

    Thanks Rupert

  2. #2
    John Francis Guest

    Stored Procedures and parameters (reply)

    On 6/23/98 11:28:23 AM, Rupert Hollom wrote:
    > I want to create a stored procedure with SQL Server 6.5 that CAN take 3
    > parameters, all of which are optional. The declaration for the stored
    > procedure is :
    CREATE PROCEDURE BackOrdersII @CustCode varchar(10),
    > @FromDate datetime, @ToDate datetime AS
    SELECT * FROM ISO_Details
    >
    WHERE DATEDIFF(dd, fldEst_Del_Date, getdate()) > 0 AND
    > CONVERT(char(12),fldActual_Del_Date,3)=`01/01/00` AND
    > CONVERT(char(12),fldEst_Del_Date,3)<>`01/01/00` AND fldDeleted<>1
    ORDER
    > BY DATEDIFF(dd, fldEst_Del_Date, getdate()) DESC

    How do I formulate the
    > procedure to allow me to select from the table, keeping the current WHERE
    > clause but adding extra items to allow the results to be further filtered
    > depending upon which of the parameters are given to the procedure. Any
    > one, two, or all three parameters could be given.

    Sorry if this seems
    > like a simple question but I am only just getting into using stored
    > procedures.

    Thanks Rupert


    You might try building your SQL statement dynamically and using the execute statement to run it. Also, check out the "Create procedure" statement to see how to make your parameters optional by supplying defaults.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •