Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Use a select query to assign parameters to a stored procedure Expand / Collapse
Author
Message
Posted Thursday, July 10, 2014 12:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 4:50 PM
Points: 25, Visits: 176
Hi,

I have a stored procedure that accepts 3 parameters - Date, Location and Item and returns the details of the last receipt of the item before the given date for that location. I now need to find the last received cost for a series of transactions (around 5200 lines) that I pull out using a query. These are transfers from our warehouse to a branch and I need to have the cost applicable per line.

My query to select the three parameters is something like this

SELECT x.xfer_no, x.from_loc, x.to_loc, x.date_shipped, x.date_entered, x.date_recvd, xl.line_no, xl.part_no, xl.description, xl.shipped, xl.qty_rcvd, xl.UoM, U.Description AS UoM_Desc, xl.conv_factor, xl.cost, IL.avg_cost
FROM xfers x
INNER JOIN xfer_list xl ON x.xfer_no = xl.xfer_no
INNER JOIN UOM_list U ON xl.UoM = U.UoM
INNER JOIN inv_list IL ON IL.Location = 'xxx' AND IL.Part_no = xl.part_no
WHERE x.to_loc = 'xxx'

I want to use the values x.date_recvd, x.to_loc and xl.part_no to be the date, location and item parameter. I then have to add the output of the stored procedure as the last value in the above query so that I am able to cost the transfer.

I am totally lost... any tips.

pssudarshan
Post #1591019
Posted Monday, July 14, 2014 10:34 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 4:50 PM
Points: 25, Visits: 176
I have found a solution of sorts for this issue. It is quite messy with a couple of temp tables etc but it appears to work. I am sure some good coding experts can actually make it much smoother and faster. Here is my code

SELECT idno = 0, x.xfer_no, x.from_loc, x.to_loc, x.date_shipped, x.date_entered, x.date_recvd, xl.line_no, xl.part_no, xl.description,
xl.shipped, xl.qty_rcvd, xl.UoM, U.Description AS UoM_Desc, xl.conv_factor, xl.cost, IL.avg_cost
INTO #Xfer_Details
FROM xfers x
INNER JOIN xfer_list xl ON x.xfer_no = xl.xfer_no
INNER JOIN UOM_list U ON xl.UoM = U.UoM
INNER JOIN inv_list IL ON IL.Location = 'TAV1' AND IL.Part_no = xl.part_no
WHERE x.to_loc = 'TAV1'

GO
-- Update IDNo field in temp table #Xfer_details

DECLARE @ID INT
SET @ID = 0
UPDATE #Xfer_details
SET @ID = idno =@id+1

GO

-- Create Temp table #Xfer_cost


CREATE TABLE #Xfer_Cost (
xfer_date datetime,
xfer_no INT,
xfer_line INT,
From_Loc VARCHAR(10),
Item VARCHAR(16),
Item_Description VARCHAR(55),
Item_UoM VARCHAR(2),
Receipt_Date VARCHAR(10),
Last_Receipt_no INT,
Branch VARCHAR(5),
Qty_Received DECIMAL(28,8),
Receipt_UoM CHAR(2),
UoM_Description VARCHAR(30),
Conv_Factor DECIMAL(28,8),
GroupCode VARCHAR(30),
Resource_Type VARCHAR(30),
Void VARCHAR(3),
PO_no INT,
PO_TaxCode VARCHAR(20),
Unit_Cost DECIMAL(28,8)
)

-- Populate #Xfer_cost - setting parameter values

DECLARE @IntFlag INT
DECLARE @MaxNo INT
SELECT @MaxNo = MAX(IDno) FROM #Xfer_Details

SET @IntFlag = 1
WHILE (@IntFlag <= @MaxNo)
BEGIN
DECLARE @Date DateTime
DECLARE @Item VARCHAR(16)
DECLARE @Location VARCHAR(16)
DECLARE @xfer_no INT
DECLARE @xfer_line INT
DECLARE @From_loc VARCHAR(10)
DECLARE @Xfer_date Datetime

SELECT @date =x.date_shipped , @Item = x.part_no, @Location = x.from_loc, @xfer_no = x.xfer_no, @xfer_line = x.line_no, @from_loc = x.from_loc, @xfer_date = x.date_shipped
FROM #Xfer_Details x
WHERE x.Idno = @IntFlag

-- Populating #Xfer_cost - Calling stored procedure

INSERT INTO #xfer_cost
EXEC SnS_LastPriceByItem @Date, @item, @location, @xfer_no, @xfer_line, @from_loc, @xfer_date
SET @IntFlag = @IntFlag + 1
END
GO
-- Final Select from #Xfer_Cost

SELECT x.IDno, x.xfer_no, x.line_no, x.from_loc, x.to_loc, x.date_shipped, x.date_recvd, x.part_no, x.description, x.shipped, x.qty_rcvd, x.UoM, x.UoM_Desc, x.conv_factor,
x.cost, x.avg_cost, c.branch, c.receipt_date, c.last_receipt_no, c.qty_received, c.receipt_uom, c.uom_description, c.conv_factor, c.groupcode, c.resource_type,
c.po_no, c.po_taxcode, c.unit_cost, VEPPieceCost =
CASE WHEN PO_TaxCode = 'INPVEP' THEN unit_cost/c.conv_factor
WHEN PO_TaxCode = 'INPVIP' THEN (unit_cost/c.conv_factor)/1.15
WHEN PO_TaxCode = 'EXEMPT' THEN unit_cost/c.conv_factor
END
FROM #xfer_Details X
LEFT OUTER JOIN #xfer_cost C ON X.xfer_no = C.xfer_no AND X.line_no = C.xfer_line

-- Clean up temp tables
DROP TABLE #Xfer_Details
DROP TABLE #Xfer_Cost


All constructive comments are invited.
Post #1592406
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse