SQL Server Performance

Stored Proceedure does not return results "HELP"

Discussion in 'General Developer Questions' started by pharoah35, Jun 30, 2003.

  1. pharoah35 New Member

    Good morning Ladies & Gents

    The following Stored Proceedure had been returning results then sudenly stoped.
    Would you be able to decipher what may be incorrect with the sp

    ________________________________________________________________________________
    Alter Procedure sp_training_test (@Start_Date As DateTime, @End_Date As DateTime)
    /*
    (
    @parameter1 datatype = default value,
    @parameter2 datatype OUTPUT
    )
    */
    As



    SELECT [HISTRY INV-LINE].INVOICE#,
    [HISTRY INV-HEADER].[INVOICE-DATE],
    [HISTRY INV-LINE].[QTY-SHP],
    [IDEAS INVENTORY].[VNDR-ITEM#],
    [IDEAS INVENTORY].[DESC] AS DESCRIPTION,
    [HISTRY INV-LINE].[DLR-PRICE] / 100 AS [UNIT SALE],
    [HISTRY INV-LINE].[QTY-SHP] * [HISTRY INV-LINE].[DLR-PRICE] / 100 AS [EXT SALE],
    [HISTRY INV-LINE].[QTY-SHP] * ([HISTRY INV-LINE].[DLR-PRICE] / 100 - [HISTRY INV-LINE].[DLR-PRICE] / 100) AS PROFIT, @Start_Date, @End_Date
    FROM dbo.[HISTRY INV-LINE] INNER JOIN
    dbo.[HISTRY INV-HEADER] ON
    dbo.[HISTRY INV-LINE].INVOICE# = dbo.[HISTRY INV-HEADER].INVOICE#
    INNER JOIN
    dbo.[IDEAS INVENTORY] ON
    dbo.[HISTRY INV-LINE].DP# = dbo.[IDEAS INVENTORY].DP#
    WHERE (dbo.[IDEAS INVENTORY].BRAND = 'training') AND
    [HISTRY INV-HEADER].[INVOICE-DATE] >= @Start_Date AND
    [HISTRY INV-HEADER].[INVOICE-DATE] <= @End_Date and
    [HISTRY INV-LINE].[QTY-SHP] <> 0

    ORDER BY [HISTRY INV-HEADER].[INVOICE-DATE], [HISTRY INV-LINE].INVOICE#

    COMPUTE
    SUM([HISTRY INV-LINE].[QTY-SHP] * [HISTRY INV-LINE].[DLR-PRICE] / 100),
    SUM([HISTRY INV-LINE].[QTY-SHP] * [HISTRY INV-LINE].[DLR-PRICE] / 100),
    SUM([HISTRY INV-LINE].[QTY-SHP] * ([HISTRY INV-LINE].[DLR-PRICE] / 100 - [HISTRY INV-LINE].[DLR-PRICE] / 100))
    __________________________________________________________________________________________

    As always, Thanks in advance
    pharoah35
  2. bambola New Member

    How do you know it is not returning results? Did you check it in QA?
    Try to add SET NOCOUNT ON at the beginning of the procedure.
    You could try to remark the conditions one at a time to see if one of them is causing the problem. Also, check to see what's in your parameters.

    Bambola.
  3. vbkenya New Member

    Sudden failure to return results will occur in the following conditions (from analysis of your stored procedure)


    • Changed Collation to a case sensitive one either at the Server,DB or column level- dbo.[IDEAS INVENTORY].BRAND = 'training'). Any recent schema changes?

    • No matching results in any of the INNER joined tables.

    • Out of range parameter values - The start and end dates, [HISTRY INV-LINE].[QTY-SHP]

    • No data at all !

    Remember that this query will return multiple result-sets (because of the COMPUTE clause). By "suddenly stopped", do you mean that

    • Not all the result-sets are being returned?

    • or nothing actually comes back from the SP?.

    Nathan H.O.
  4. gaurav_bindlish New Member

    I agree with previous two posts in the forum.

    Also I have some comments -
    SUM([HISTRY INV-LINE].[QTY-SHP] * [HISTRY INV-LINE].[DLR-PRICE] / 100) is written twice in COMPUTE.

    Since you are using COMPUTE, u'll have to give the column names after BY statement. So the query should look like-
    Alter Procedure sp_training_test (@Start_Date As DateTime, @End_Date As DateTime)
    /*
    (
    @parameter1 datatype = default value,
    @parameter2 datatype OUTPUT
    )
    */
    As



    SELECT [HISTRY INV-LINE].INVOICE#,
    [HISTRY INV-HEADER].[INVOICE-DATE],
    [HISTRY INV-LINE].[QTY-SHP],
    [IDEAS INVENTORY].[VNDR-ITEM#],
    [IDEAS INVENTORY].[DESC] AS DESCRIPTION,
    [HISTRY INV-LINE].[DLR-PRICE] / 100 AS [UNIT SALE],
    [HISTRY INV-LINE].[QTY-SHP] * [HISTRY INV-LINE].[DLR-PRICE] / 100 AS [EXT SALE],
    [HISTRY INV-LINE].[QTY-SHP] * ([HISTRY INV-LINE].[DLR-PRICE] / 100 - [HISTRY INV-LINE].[DLR-PRICE] / 100) AS PROFIT,
    @Start_Date, @End_Date
    FROM dbo.[HISTRY INV-LINE] INNER JOIN
    dbo.[HISTRY INV-HEADER] ON
    dbo.[HISTRY INV-LINE].INVOICE# = dbo.[HISTRY INV-HEADER].INVOICE#
    INNER JOIN
    dbo.[IDEAS INVENTORY] ON
    dbo.[HISTRY INV-LINE].DP# = dbo.[IDEAS INVENTORY].DP#
    WHERE (dbo.[IDEAS INVENTORY].BRAND = 'training') AND
    [HISTRY INV-HEADER].[INVOICE-DATE] >= @Start_Date AND
    [HISTRY INV-HEADER].[INVOICE-DATE] <= @End_Date and
    [HISTRY INV-LINE].[QTY-SHP] <> 0

    ORDER BY [HISTRY INV-HEADER].[INVOICE-DATE], [HISTRY INV-LINE].INVOICE#

    COMPUTE
    SUM([HISTRY INV-LINE].[QTY-SHP] * [HISTRY INV-LINE].[DLR-PRICE] / 100),
    SUM([HISTRY INV-LINE].[QTY-SHP] * ([HISTRY INV-LINE].[DLR-PRICE] / 100 - [HISTRY INV-LINE].[DLR-PRICE] / 100))
    BY
    [HISTRY INV-LINE].INVOICE#,
    [HISTRY INV-HEADER].[INVOICE-DATE],
    [IDEAS INVENTORY].[VNDR-ITEM#],
    [IDEAS INVENTORY].[DESC],
    @Start_Date, @End_Date

    Tell me if this makes sense....

    Gaurav
  5. rushmada New Member

    Have u recently added this line in the proc, i.e

    HISTRY INV-LINE].[QTY-SHP] <> 0

    If so comment this line in the proc and alter the proc and test it. If it is
    working fine then try to change the line as

    HISTRY INV-LINE].[QTY-SHP] not in (0)


    Thanks

    Rushendra

Share This Page