39
Hi. I need to create a new tableadapter retrieving data from DB using stored procedure.
When i wrote stored procedure script in the stored procedure wizard and click next(or finish) it's generate an error that it can not parse sql statment.
What do i need to change in the script to make it functional:
NOTE:stored procedure is fully functional in SQL SERVER 2005(calling from e.g. QA)
"
CREATE PROCEDURE dbo.spMDB_R_Chargeability_by_BDC
@Period DateTime,
@Type smallint
AS
DECLARE @MStart datetime, @MEnd datetime, @YStart datetime
SET @MStart = dbo.fnMDB_GetPeriodStart(@Period,@Type)
SET @MEnd = dbo.fnMDB_GetPeriodEnd(@Period,@Type)
SET @YStart = dbo.fnMDB_YearStart(@period)
SELECT MDB_V_Chargeability_BDC.[ID],
CASE SUM([A Sum])
WHEN NULL THEN 0
ELSE ROUND(SUM([C Sum]) / SUM([A sum]) * 100,2)
END as [C/A (%) (V)],
CASE SUM([A Sum])
WHEN NULL THEN 0
ELSE ROUND(SUM([M Sum]) / SUM([A Sum]) * 100,2)
END as [M/A (%) (V)],
SUM([C Sum]) as [C (V)],
SUM([M Sum]) as [M (V)],
SUM([A Sum]) as [A (V)],
CASE [A (Y)]
WHEN NULL THEN 0
ELSE ROUND([C (Y)] / [A (Y)] * 100,2)
END as [C/A (%) (Y)],
CASE [A (Y)]
WHEN NULL THEN 0
ELSE ROUND([M (Y)] / [A (Y)] * 100,2)
END as [M/A (%) (Y)],
[C (Y)], [M (Y)], [A (Y)]
FROM MDB_V_Chargeability_BDC
INNER JOIN
(SELECT [ID], ROUND(SUM([C sum]),2) as [C (Y)], ROUND(SUM([A sum]),2) as [A (Y)], ROUND(SUM([M sum]),2) as [M (Y)]
FROM MDB_V_Chargeability_BDC
WHERE MDB_Period >= @YStart and MDB_Period <= @period
GROUP BY [ID]
) as CHARGEABILITY_BY_YEAR on (CHARGEABILITY_BY_YEAR.[ID] = MDB_V_Chargeability_BDC.[ID])
WHERE
MDB_Period >= @MStart and MDB_Period <= @MEnd
GROUP BY MDB_V_Chargeability_BDC.[ID], [A (Y)],[C (Y)],[M (Y)]"
