Help with Stored Procedure

  • I have created a stored procedure that uses a temp table to do some updates.

    the problem is that the update statement outside the stored procedure works fine , but inside does not fetches any results. My SP code is listed below

    Can you help please?

    Thanks

    ALTER PROCEDURE [dbo].[ProductAnalysis]

    @PeriodId int

    AS

    BEGIN

    SET NOCOUNT ON

    DECLARE @TMP1 TABLE

    (

    PeriodId int,

    ProductTypeId int,

    ProductId int,

    NumOfProducts int,

    TotalRevenue numeric(21, 6),

    )

    --------------------------------------------------------------------------------------------------------------------

    --------------------------------------------------------------------------------------------------------------------

    /*

    Gather all possible combinations

    */

    insert @TMP1 (

    PeriodId,

    ProductTypeId,

    ProductId,

    NumOfProducts,

    TotalRevenue)

    SELECT @PeriodId as PeriodId

    ,ProductTypeId,

    ProductId,0,0

    FROM dbo.Products a

    JOIN PD_Rev b

    on a.ProductId = b.ProductId

    GROUP BY a.ProductTypeId,

    a.ProductId

    --------------------------------------------------------------------------------------------------------------------

    --------------------------------------------------------------------------------------------------------------------

    /*

    Update Set No Of Products ,Total Revenue

    */

    ;WITH

    TBL1 ( ProductTypeId,ProductId,NumOfProducts,TotalRevenue)

    AS(

    SELECT

    ,ProductTypeId,

    ProductId,

    ,count(NumOfProducts) as NumOfProducts

    ,sum(b.Revenue) as TotalRevenue

    FROM dbo.Products a

    JOIN PD_Rev b

    on a.ProductId = b.ProductId

    GROUP BY a.ProductTypeId,

    a.ProductId

    )

    UPDATE @TMP1

    SETNumOfProducts = ISNULL(b.NumOfProducts,0) ,

    TotalRevenue = ISNULL(b.TotalRevenue,0)

    FROM @TMP1 as a

    JOIN TBL1 as b

    ON a.ProductTypeId = b.ProductTypeId

    AND a.ProductId = b.ProductId

    END

  • Add

    SELECT * FROM @TMP1

    Just before the "END".


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thank you !:-)

    can you explain my why pls?

  • I could, but I'd rather hear what you think then explain it to you if you're wrong.

    Let's break down what your stored procedure does: -

    * Your stored procedure begins

    * You've declared a table variable inside your stored procedure.

    * You fill the table variable with data inside your stored procedure.

    * You update the data inside your stored procedure.

    * Then your stored procedure ends. This finishes the batch where your table variable was created.

    You aren't doing anything to return the data in your table variable and once your stored procedure finishes the batch is complete.


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Thank you both for replies and advices.

  • NiKo22 (8/31/2012)


    I have created a stored procedure that uses a temp table to do some updates.

    the problem is that the update statement outside the stored procedure works fine , but inside does not fetches any results. ...

    The stored procedure doesn't fail to return results, it doesn't compile:

    SELECT

    ,ProductTypeId,

    ProductId,

    ,count(NumOfProducts) as NumOfProducts

    Subbing out the unnecessary table variable yields this:

    SELECT

    @PeriodId as PeriodId,

    a.ProductTypeId,

    a.ProductId,

    NumOfProducts = d.NumOfProducts,

    TotalRevenue = d.TotalRevenue

    FROM dbo.Products a

    INNER JOIN PD_Rev b

    ON a.ProductId = b.ProductId

    INNER JOIN (

    SELECT

    ProductTypeId,

    ProductId,

    count(NumOfProducts) as NumOfProducts,

    sum(b.Revenue) as TotalRevenue

    FROM dbo.Products a

    JOIN PD_Rev b

    on a.ProductId = b.ProductId

    GROUP BY a.ProductTypeId,

    a.ProductId

    ) d

    ON a.ProductTypeId = d.ProductTypeId

    GROUP BY a.ProductTypeId, a.ProductId

    Which looks the same to me as this:

    SELECT

    @PeriodId as PeriodId,

    a.ProductTypeId,

    a.ProductId,

    count(NumOfProducts) as NumOfProducts,

    sum(b.Revenue) as TotalRevenue

    FROM dbo.Products a

    INNER JOIN PD_Rev b

    ON a.ProductId = b.ProductId

    GROUP BY @PeriodId, a.ProductTypeId, a.ProductId

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply