Sql server - how to insert single row into temporary table?

Question

Hello, I have two temporary table, when i do cycle through one table and i get some values from it, in this cycle I need insert new row into another temporary table. Is this possible. Here is my sql code and error information:

Alter PROCEDURE ProfitReportQ_Search_WithSub 
(@DateFrom datetime,
 @DateTo datetime,
 @DateActive bit,
 @UserID int,
 @ItemGroupIDValues nvarchar(max)
)
AS
BEGIN
CREATE TABLE #tmp(ItemGroupID int, ItemGroupName nvarchar(250), Manager nvarchar(250), AllQuantity int, AllSumPrice AllSumPrice, AllSumPriceWithVAT decimal(18,4), Profit decimal(18,4))
CREATE TABLE #tmp2(Manager nvarchar(250), AllQuantity int, AllSumPrice decimal(18,4), AllSumPriceWithVAT decimal(18,4), Profit decimal(18,4), ItemGroupNameRoot nvarchar(250))


INSERT INTO #tmp
 EXEC ProfitReportQ_Search @DateFrom, @DateTo, @DateActive, @UserID, @ItemGroupIDValues

DECLARE @ItemGroupID int
DECLARE @ItemGroupName nvarchar(250)
DECLARE @Manager nvarchar(250)
DECLARE @AllQuantity int
DECLARE @AllSumPrice decimal(18,4)
DECLARE @AllSumPriceWithVAT decimal(18,4)
DECLARE @Profit decimal(18,4)
DECLARE @ItemGroupNameRoot nvarchar(250)
DECLARE @count int

SET @count = (SELECT COUNT(*) FROM #tmp)
WHILE (@count <> 0)
BEGIN
 SELECT TOP (1) @ItemGroupID = ItemGroupID, @ItemGroupName = ItemGroupName, @Manager = Manager, @AllQuantity = AllQuantity, @AllSumPrice = AllSumPrice, @AllSumPriceWithVAT = AllSumPriceWithVAT, @Profit = Profit FROM #tmp
 DELETE #tmp WHERE ItemGroupID = ItemGroupID AND ItemGroupName = @ItemGroupName AND Manager = @Manager AND AllQuantity = @AllQuantity AND AllSumPrice = @AllSumPrice AND AllSumPriceWithVAT = @AllSumPriceWithVAT AND Profit = @Profit



 INSERT INTO #tmp2 (Manager, AllQuantity, AllSumPrice, AllSumPriceWithVAT, Profit, ItemGroupNameRoot )
  VALUES (@Manager, @AllQuantity, @AllSumPrice, @AllSumPriceWithVAT, @Profit, EXEC ItemGroup_GetRootWithRecurse @ItemGroupID)
END


SELECT ItemGroupNameRoot, Manager, SUM(AllQuantity) AS AllQuantity, SUM(AllSumPrice) AS AllSumPrice, 
  SUM(AllSumPriceWithVAT) AS AllSumPriceWithVAT, SUM(Profit) AS Profit
FROM #tmp2
GROUP BY ItemGroupNameRoot, Manager

DELETE #tmp
DELETE #tmp2

END
GO

There is a problem with this lines:

 INSERT INTO #tmp2 (Manager, AllQuantity, AllSumPrice, AllSumPriceWithVAT, Profit, ItemGroupNameRoot )
  VALUES (@Manager, @AllQuantity, @AllSumPrice, @AllSumPriceWithVAT, @Profit, EXEC ItemGroup_GetRootWithRecurse @ItemGroupID)

Error:

Incorrect syntax near the keyword 'EXEC'. Incorrect syntax near ')'.

Some ideas?

Answer 1

It appears that you are trying to insert the result of the following into a #tmp2's ItemGroupNameRoot column:

EXEC ItemGroup_GetRootWithRecurse @ItemGroupID

You really can't execute stored procedures and use results that way. Instead, I suggest replacing the ItemGroup_GetRootWithRecurse stored procedure with a user defined function. It might looking something like this:

CREATE FUNCTION ItemGroup_GetRootWithRecurse_Function (@ItemGroupID int)
RETURNS varchar(30)
AS
BEGIN
declare @Return varchar(30)
select @return = case @ItemGroupID
when 1 then 'One'
when 2 then 'Two'
else 'Three'
end

return @return
end

You would call the function nearly the same way as the stored procedure. The big difference is you drop the EXEC statement so the call looks like this:

dbo.ItemGroup_GetRootWithRecurse_Function (@ItemGroupID)

I hope this helps.

Answer 2

short answer:
You can share a temp table created in a stored procedure with a stored procedure that it calls. You could try to use this technique to insert into #temp2 within ItemGroup_GetRootWithRecurse.

long answer:
this may help: How to Share Data Between Stored Procedures. You can read the entire article, but the linked section on sharing temp tables between procedures my do the trick.

you could pass @Manager, @AllQuantity, @AllSumPrice, @AllSumPriceWithVAT, @Profit as well as @ItemGroupID into ItemGroup_GetRootWithRecurse, and within, insert into #tmp2 with something like:

WITH recurseUp AS 
(
SELECT ItemGroupID, ParentID, ItemGroupName 
FROM dbo.ItemGroup 
WHERE ItemGroupID = @ItemGroupID 

UNION ALL 
SELECT b.ItemGroupID, b.ParentID, b.ItemGroupName 
FROM recurseUp AS a 
INNER JOIN dbo.ItemGroup AS b ON a.ParentID = b.ItemGroupID
) 
INSERT INTO #temp2 (Manager, AllQuantity, AllSumPrice, AllSumPriceWithVAT, Profit, ItemGroupNameRoot )
SELECT @Manager, @AllQuantity, @AllSumPrice, @AllSumPriceWithVAT, @Profit, ItemGroupName 
FROM recurseUp AS recurseUp_1 WHERE (ParentID IS NULL) END

this code may be a little off, since I don't have al the details, but should be close.

Answer 3

It was not possible for me to put these values into #temp2 when it was execute in another SP. I worked out with three temporary tables, like this:

BEGIN

CREATE TABLE #tmp(ItemGroupID int, ItemGroupName nvarchar(250), 
     Manager nvarchar(250), AllQuantity int, 
     AllSumPrice decimal(18,4), AllSumPriceWithVAT decimal(18,4), 
     Profit decimal(18,4), ID int
     )
CREATE TABLE #tmp2(Manager nvarchar(250), AllQuantity int, 
     AllSumPrice decimal(18,4), AllSumPriceWithVAT decimal(18,4), 
     Profit decimal(18,4), ItemGroupNameRoot nvarchar(250)
     )

CREATE TABLE #tmp3(ItemGroupNameRoot nvarchar(250))


INSERT INTO #tmp
 EXEC ProfitReportQ_Search_v2 @DateFrom, @DateTo, @DateActive, @UserID, @ItemGroupIDValues

DECLARE @ID int
DECLARE @ItemGroupID int
DECLARE @ItemGroupName nvarchar(250)
DECLARE @Manager nvarchar(250)
DECLARE @AllQuantity int
DECLARE @AllSumPrice decimal(18,4)
DECLARE @AllSumPriceWithVAT decimal(18,4)
DECLARE @Profit decimal(18,4)
DECLARE @ItemGroupNameRoot nvarchar(250)
DECLARE @count int

SET @count = (SELECT COUNT(*) FROM #tmp)
WHILE (@count <> 0)
BEGIN
 SELECT TOP (1) @ItemGroupID = ItemGroupID, @ItemGroupName = ItemGroupName, @Manager = Manager, @AllQuantity = AllQuantity, @AllSumPrice = AllSumPrice, @AllSumPriceWithVAT = AllSumPriceWithVAT, @Profit = Profit, @ID = ID 
 FROM #tmp
 DELETE #tmp WHERE ID = @ID

 INSERT INTO #tmp3 EXEC ItemGroup_GetRootWithRecurse_ForProfitReport @ItemGroupID
 SELECT TOP(1)@ItemGroupNameRoot = ItemGroupNameRoot 
 FROM #tmp3


 INSERT INTO #tmp2 SELECT @Manager, @AllQuantity, 
     @AllSumPrice, @AllSumPriceWithVAT, 
     @Profit, @ItemGroupNameRoot
 DELETE #tmp3
 SET @count = (SELECT COUNT(*) FROM #tmp)
END
DELETE #tmp
SELECT ItemGroupNameRoot, Manager, SUM(AllQuantity) AS AllQuantity, SUM(AllSumPrice) AS AllSumPrice, 
  SUM(AllSumPriceWithVAT) AS AllSumPriceWithVAT, SUM(Profit) AS Profit
FROM #tmp2
GROUP BY ItemGroupNameRoot, Manager
DELETE #tmp2

END GO