August 31, 2012 at 4:47 am
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
August 31, 2012 at 4:51 am
Add
SELECT * FROM @TMP1
Just before the "END".
August 31, 2012 at 6:23 am
Thank you !:-)
can you explain my why pls?
August 31, 2012 at 7:26 am
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.
September 3, 2012 at 3:00 am
Thank you both for replies and advices.
September 3, 2012 at 4:05 am
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
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