[dech]

Ок, вот готовая процедура, которая у нас уже довольно давно используется в SSRS отчете:

USE [PRD]
GO
/****** Object:  StoredProcedure [dbo].[usp_ReportEOLYield]    Script Date: 08/16/2013 05:24:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_ReportEOLYield]
--DECLARE
        @StartDate DATETIME
      , @EndDate DATETIME
      , @WorkCenters VARCHAR(MAX)
      , @Shifts VARCHAR(MAX)
AS
BEGIN
--SET @StartDate = '2012-09-23'
--SET @EndDate = '2012-10-23'
--SET @WorkCenters = 'V01,V02'
--SET @Shifts = 'A,B,C,D'

--SET @StartDate = '2012-04-01 21:00:00'
--SET @EndDate = '2012-04-01 22:00:00'
--SET @WorkCenters = 'E02,E03,E04'
--SET @Shifts = 'A,B,C,D'

DECLARE @WorkCenterTable TABLE(ListItem VARCHAR(100) COLLATE Latin1_General_CI_AI)
DECLARE @ShiftTable TABLE(ListItem VARCHAR(100) COLLATE Latin1_General_CI_AI)
INSERT INTO @WorkCenterTable SELECT ListItem FROM QALab.[dbo].[SplitListItemsIntoTable](@WorkCenters)
INSERT INTO @ShiftTable SELECT ListItem FROM QALab.[dbo].[SplitListItemsIntoTable](@Shifts)

SET NOCOUNT ON

IF OBJECT_ID('Temp_DB..#EOLTable') IS NOT NULL DROP TABLE #EOLTable
	
CREATE TABLE #EOLTable(
      WorkCenter varchar(10) COLLATE Latin1_General_CI_AI
      , Prod varchar(20) COLLATE Latin1_General_CI_AI
      , Item varchar(20) COLLATE Latin1_General_CI_AI
      , Job varchar(20) COLLATE Latin1_General_CI_AI
      , Serial varchar(20) COLLATE Latin1_General_CI_AI
      , FeedStock varchar(20) COLLATE Latin1_General_CI_AI
      , Date datetime
      , Qty numeric(28,12)
      , UOM varchar(15) COLLATE Latin1_General_CI_AI
      , Scrap numeric(28,12)
      , ScrapCode varchar(20) COLLATE Latin1_General_CI_AI
      , Descript varchar(50) COLLATE Latin1_General_CI_AI
      , WrkCtrGroup varchar(10) COLLATE Latin1_General_CI_AI
      , InventTransId varchar(20) COLLATE Latin1_General_CI_AI
)

INSERT INTO #EOLTable(WorkCenter, Prod, Item, Job, Serial, Date, Qty, UOM, Scrap, ScrapCode,
	Descript, WrkCtrGroup,
	InventTransId)
SELECT SFMatl.WrkCtrId
	, ProdTable.DIMENSION2_
	, RTRIM(CASE WHEN SFMatl.ItemId LIKE 'Waste%' THEN '' ELSE SFMatl.ItemId END)
		+ CASE(RTRIM(ColorId)) WHEN '' THEN '' ELSE '-' + RTRIM(SizeId) + '-' + RTRIM(ColorId) END
	, UPPER(RTRIM(SFMatl.ProdId))
	, ppoSerialNum
	, ShiftTime
	, CASE WHEN SFMatl.WasteId <> '' AND TranType = 'OFF-WST' OR SFMatl.ItemId LIKE 'Waste%' THEN 0 ELSE StkQty END
	, StkUnit
	, CASE WHEN SFMatl.WasteId <> '' AND TranType = 'OFF-WST' OR SFMatl.ItemId LIKE 'Waste%' THEN StkQty ELSE 0 END
	, CASE WHEN SFMatl.ItemId LIKE 'Waste%' AND SFMatl.WasteId = '' THEN SFMatl.ItemId ELSE SFMatl.WasteId END
	, WasteCaption
	, WrkCtrTable.WrkCtrGroupId
	, SFMatl.InventTransId
FROM CSF_PRD.dbo.SFMatl SFMatl WITH (NOLOCK)
INNER JOIN CSF_PRD.dbo.WasteId WasteId
   ON WasteId.WasteId = SFMatl.WasteId -- Old behaviour: CASE WHEN ItemId LIKE 'Waste%' AND SFMatl.WasteId = '' THEN ItemId ELSE SFMatl.WasteId END
INNER JOIN ProdTable
   ON ProdTable.DataAreaId = SFMatl.CompanyId
  AND ProdTable.ProdId = SFMatl.ProdId
INNER JOIN WrkCtrTable
   ON WrkCtrTable.DataAreaId = ProdTable.DataAreaId
  AND WrkCtrTable.WrkCtrId = SFMatl.WrkCtrId
WHERE
      SFMatl.CompanyId = 'clt'
	  AND ProdTable.DIMENSION3_ = 'FSM'
      AND RTRIM(ColorId) = ''
      AND SFMatl.TranType IN ('OFF-FIN','OFF-WST')
      AND ShiftTime >= @StartDate
      AND ShiftTime < @EndDate
	  AND WrkCtrTable.WrkCtrGroupId NOT IN ('FSMSLIT','FSMREW','FSMDEP2','FSMDEP1','CCDFSMSLIT','CCDFSMREW','CCDFSMDEP2','CCDFSMDEP1')
	  AND [dbo].[udfGetShift] (ShiftTime) IN (SELECT ListItem FROM @ShiftTable)
      AND SFMatl.WrkCtrId IN (SELECT ListItem FROM @WorkCenterTable)
      AND SerialNum IN (SELECT Matl.SerialNum FROM CSF_PRD.dbo.SFMatl AS Matl
						WHERE Matl.ContainerNum = SFMatl.ContainerNum
						  AND Matl.ppoSerialNum = SFMatl.ppoSerialNum
						  AND Matl.PrOdId = SFMatl.PrOdId
						GROUP BY Matl.SerialNum
						HAVING SUM(Matl.StkQty) <> 0);

WITH MyCTE(Item, SerialNum) AS 
	(SELECT pmtOn.Item, pmtOff.SerialNum FROM ppoProdMatlTrace pmtOff WITH (NOLOCK) 
		INNER JOIN ppoProdMatlTrace pmtOn WITH (NOLOCK) 
		   ON pmtOff.DataAreaId = pmtOn.DataAreaId 
		  AND pmtOff.PrOdSet = pmtOn.PrOdSet 
		  AND pmtOff.Ply = pmtOn.MakingPly 
		  AND pmtOff.Type = 0 
		  AND pmtOn.Type = 1
		WHERE pmtOff.DataAreaId = N'clt'
		  AND pmtOff.SERIALNUM IN (SELECT DISTINCT SERIAL FROM #EOLTable))
		  
UPDATE #EOLTable
SET FeedStock = (SELECT MAX(Item) FROM MyCTE
					WHERE MyCTE.SerialNum = Serial)
	, Item = CASE WHEN Item = '' OR LEFT(Item, 1) = '-' THEN 
		(SELECT MAX(ItemId) FROM InventTrans 
			WHERE InventTrans.DataAreaId = N'clt' 
			  AND InventTrans.InventTransId = #EOLTable.InventTransId) + Item
		ELSE Item END

SELECT * FROM #EOLTable

--DROP TABLE #EOLTable
END

Мне нужно всего-то добавить еще 1 параметр и пару полей во временную таблицу для вывода новых данных. И все это дело выгружать в эксель. Но никак не получается вызвать её правильно.