• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 446
  • Last Modified:

How could I insert data into a temp table that is coming back from a Stored Procedure?

I have the Insert Into  before the I call the stored Procedure. The parameters with the values are coming back but the table does not get updated.
      
            
                        INSERT INTO @BestMoveFromTo (LocationFromName ,LocationToName, OccupiedPos, Capacity, NumberOfMoves, NumberofFreeSpaces, Percentage)

                        EXEC [WMS.Handling].[GetPalletMoveEfficiency] @LocationFromName ,@LocationToName, @OccupiedPos OUT,      @Capacity OUT,
                              @NumberOfMoves OUT, @NumberofFreeSpaces OUT, @Percentage OUT      
      
Thank you
0
iscivanomar
Asked:
iscivanomar
  • 5
  • 2
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
it should return only if you have at most one select statement that returns the data
0
 
iscivanomarAuthor Commented:
Provably is where I have the mistake. This is the stored procedure that I am calling.

I am assigning the result into a variables.

USE [WMS_KRAFT750_DEV]
GO
/****** Object:  StoredProcedure [WMS.Handling].[GetPalletMoveEfficiency]    Script Date: 10/10/2011 17:13:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:            <Ivan De Santiago Salazar>
-- Create date: 2011-07-10
-- Description:      Get Occupied Position, Capacity, Numbero of Moves, Number of Free Spaces and Efficiency of a specific Location
-- =============================================
ALTER PROCEDURE [WMS.Handling].[GetPalletMoveEfficiency]
--OTHER INPUT PARAMETERS.
      @LocationFrom VARCHAR(50),      
      @LocationTo VARCHAR(50),
--OTHER OUTPUT PARAMETERS      
      @OccupiedPos INT OUT,
      @Capacity INT OUT,
      @NumberOfMoves NUMERIC (3,2) OUT,
      @NumberofFreeSpaces INT OUT,
      --@FullRackFree BIT OUT      ,
      @Percentage numeric(6,2) OUT      
   
AS
BEGIN            
            
      IF @LocationFrom <> '' AND @LocationTo <>''
      BEGIN      
            --insert into @StorageLocationTable (IdStorageLocation,Capacity,BusyPositions,BookedPositions,LastPositioningTime)
            SET @NumberofFreeSpaces = (select            --SL.ID AS ID_StorageLocation,
            ((sl.Capacity) -
            COUNT(sup.id) +  isnull((select SUM(NumPalletsToMove) from WMS_Kraft.AutoHKLocation where LocationNameTo = sl.Name),0) )AS BusyPositions                                                
            from            [WMS.Storage].StorageLocation sl
            inner join      [WMS.Storage].StorageType      stt
                        on      sl.Id_StorageType            =      stt.Id
            inner join      [WMS.Storage].StoragePosition sp
                        on      sp.Id_StorageLocation      =      sl.Id                                                                        
            left join      [WMS.Storage].StockUnitPosition sup
                        on      sup.Id_StoragePosition      =      sp.Id                                                      
            where      stt.Name in ('Warehouse')
                              and sp.StorageBlocked = 0
                              and sp.StorageDisabled = 0
                              and sl.StorageDisabled_LGV = 0
                              and sl.Name = @LocationFrom --@LocationFrom
            group by sl.Id, sl.Capacity, sl.Name)
                                                      
                                                      
            
            
            SET @OccupiedPos = (select                        
            COUNT(sup.id) +  isnull((select SUM(NumPalletsToMove) from WMS_Kraft.AutoHKLocation where LocationNameTo = sl.Name),0) AS BusyPositions                                                
            from            [WMS.Storage].StorageLocation sl
            inner join      [WMS.Storage].StorageType      stt
                        on      sl.Id_StorageType            =      stt.Id
            inner join      [WMS.Storage].StoragePosition sp
                        on      sp.Id_StorageLocation      =      sl.Id                                                                        
            left join      [WMS.Storage].StockUnitPosition sup
                        on      sup.Id_StoragePosition      =      sp.Id                                                      
            where      stt.Name in ('Warehouse')
                              and sp.StorageBlocked = 0
                              and sp.StorageDisabled = 0
                              and sl.StorageDisabled_LGV = 0
                              and sl.Name = @LocationFrom
            group by sl.Id, sl.Capacity, sl.Name)
            
            
            
            SET @NumberOfMoves = @OccupiedPos      
            SET @Capacity = @OccupiedPos + @NumberofFreeSpaces
            SET @Percentage = ((@NumberOfMoves/@Capacity)*100)            
      
       END
      RETURN
END      
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
there is no select statements that returns the data, and i dont think you need it either.
I am not sure why you are using a temp table as the desired values you get as an output value
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
iscivanomarAuthor Commented:
What I am trying to do is to analysed the data in this @tmp table.
I am looking for the record with less movements and the one the free more position in the warehouse.
the stored procedure give me this for each records.
At the end, I select the best record of the table by using percentage.

this is the while loop with the select asc part too.




insert into @tmp (LocationFromName,LocationToName,NumPallets,ReasonText)
                              select LocationNameFrom,LocationNameTo,NumPalletsToMove,Reason from WMS_Kraft.AutoHKLocation
                  
      
            set @count = (select COUNT(*) from @tmp)
            -----------------------------------------------------------------------------------
            --Find the best move to do according to the list just created by finding what move free's more space in the warehouse
            --Create a table with all the information about the Move with Percentage Efficiency
            WHILE (@index <=@count)
            BEGIN
                  SELECT @LocationFromName= LocationFromName, @LocationToName= LocationToName FROM @tmp where ID = @index
                  
                  IF @LocationFromName <> '' and @LocationToName <> ''
                  BEGIN
                        INSERT INTO #BestMoveFromTo-- (LocationFromName ,LocationToName, OccupiedPos, Capacity, NumberOfMoves, NumberofFreeSpaces, Percentage)
                        EXEC [WMS.Handling].[GetPalletMoveEfficiency] @LocationFromName ,@LocationToName, @OccupiedPos OUT,      @Capacity OUT,
                              @NumberOfMoves OUT, @NumberofFreeSpaces OUT, @Percentage OUT      
                  END
                  
                  set @index = @index + 1            
            END

set @count = (select COUNT(*) from #BestMoveFromTo)
            
            SELECT top 1 @LocationFromBest=LocationFromName, @LocationToBest=LocationToName FROM #BestMoveFromTo Order by Percentage asc
            
0
 
iscivanomarAuthor Commented:
I think, it is not so easy question, I am raising the pint to Max.
0
 
iscivanomarAuthor Commented:
I decided to change the strategy here
0
 
iscivanomarAuthor Commented:
Thank you for your help. I decided to get the data from the stored procedure, save it in a variable, and compare this with the last one, if this is better, I change if is better. Thank you
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now