osupratt
Posting Yak Master
238 Posts |
Posted - 2009-06-05 : 18:36:08
|
I have the following stored procedure. When I use this as my datasource in SSRS I get an error message saying that it is expecting the parameter. Does anyone know how to get this working. This is the first time I've set up a parameter from a Stored Procedure and I'm struggling bad. Any help would be greatly appreciated.
USE [Shale_Ticket] GO /****** Object: StoredProcedure [dbo].[SP_SHALE_EOG_DATEPARAM1] Script Date: 06/05/2009 17:31:48 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[SP_SHALE_EOG_DATEPARAM1] @StartDate DATETIME AS BEGIN
--DECLARE @StartDate DATETIME;
WITH DISPBBLS AS( SELECT Region,Customer,Yard, SUM(DispBBLSDay3)DispBBLSDay3,SUM(DispBBLSDay2)DispBBLSDay2,SUM(DispBBLSDay1)DispBBLSDay1,
SUM(DispBBLSDay3)+SUM(DispBBLSDay2)+SUM(DispBBLSDay1) SumThirtyDispBBLS
FROM(
SELECT
CASE WHEN Yard IN ('TOLAR', 'ALEDO', 'CLEBURNE', 'JACKSBORO') THEN 'EOG NORTH' WHEN Yard IN ('GAINESVILLE', 'STONY') THEN 'EOG WEST' ELSE 'UNKNOWN' END AS REGION, CASE WHEN Customer IN('EOG RESOURCES','EOG RESOURCES - ET','EOG RESOURCES - NT') THEN 'EOG RESOURCES' ELSE ' ' END AS Customer, FTDate, FTNumber, Yard, DisposalSite, DisposalBBLS,
CASE WHEN CONVERT(DATETIME,FTDate,102)=DATEADD(DAY, - 06 + DATEDIFF(DAY, '19000101', @StartDate), '19000101') THEN DisposalBBLS ELSE 0 END AS DispBBLSDay3, CASE WHEN CONVERT(DATETIME,FTDate,102)=DATEADD(DAY, - 05 + DATEDIFF(DAY, '19000101', @StartDate), '19000101') THEN DisposalBBLS ELSE 0 END AS DispBBLSDay2, CASE WHEN CONVERT(DATETIME,FTDate,102)=DATEADD(DAY, - 04 + DATEDIFF(DAY, '19000101', @StartDate), '19000101') THEN DisposalBBLS ELSE 0 END AS DispBBLSDay1
FROM VW_Shale_STT_LoadPerformance1 WHERE DisposalSite IS NOT NULL AND DisposalSite > ' ' AND SUBSTRING(Customer,1,3)='EOG'AND Yard IN ('TOLAR', 'ALEDO', 'CLEBURNE', 'JACKSBORO','GAINESVILLE', 'STONY') AND DisposalBBLS>0
) AS X GROUP BY Region,Customer,Yard
) SELECT Region,Customer,Yard, DispBBLSDay3,DispBBLSDay2,DispBBLSDay1, SumThirtyDispBBLS
FROM DISPBBLS
END |
|