USE [OMA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[UpdateRateTable]
(
@UpdatedRateTableList TempRateCard READONLY,
@RateTableID INT,
@OperationParameter INt
)
AS
BEGIN
DELETE FROM TempRateCardDetails
INSERT INTO [dbo].[TempRateCardDetails]
;WITH m(id, ratetableid, ratetabletype, ratetablevalue, low, target, high, id1,
customdetails, rt1, ratecategoryid1, ratecategoryid2, ratetablevalue1)
AS (SELECT *
FROM (SELECT id,
ratetableid,
ratetabletype,
ratetablevalue,
low,
target,
high,
datapointscount,
customfields
FROM (SELECT id,
ratetableid,
ratecategoryid1,
ratecategoryid2,
ratecategoryid3,
ratecategoryid4,
ratecategoryid5,
ratecategoryid6,
ratecategoryid7,
ratecategoryid8,
ratecategoryid9,
ratecategoryid10,
Cast(customfields AS NVARCHAR(max)) AS
CustomFields,
Cast(low AS NVARCHAR(max)) AS Low,
Cast(target AS NVARCHAR(max)) AS Target
,
Cast(high AS NVARCHAR(max)) AS High,
Cast(datapointscount AS NVARCHAR(max)) AS
DataPointsCount
FROM tempratecarddetails) UploadedRateTables
UNPIVOT (ratetablevalue
FOR ratetabletype IN (ratecategoryid1,
ratecategoryid2,
ratecategoryid3,
ratecategoryid4,
ratecategoryid5,
ratecategoryid6,
ratecategoryid7,
ratecategoryid8,
ratecategoryid9,
ratecategoryid10) ) AS
rates)P1
INNER JOIN (SELECT 'RateCategoryID1' AS RateCategoryType,
C1.ratecategoryid1 AS RateCategoryID,
C1.ratetableid,
C1.value
FROM ratetablecategory1 C1
INNER JOIN tempratecarddetails T
ON C1.value LIKE T.ratecategoryid1
AND T.ratetableid = C1.ratetableid
UNION
SELECT 'RateCategoryID2' AS RateCategoryType,
C2.ratecategoryid2 AS RateCategoryID,
C2.ratetableid,
C2.value
FROM ratetablecategory2 C2
INNER JOIN tempratecarddetails T
ON C2.value LIKE T.ratecategoryid2
AND T.ratetableid = C2.ratetableid
)P2
ON P1.ratetabletype = P2.ratecategorytype
AND P1.ratetablevalue = P2.value)
SELECT P1.id,
P1.ratecategoryid1,
P1.ratecategoryid2,
P1.castrdid,
P1.ratecategoryid3,
P1.ratecategoryid4,
P1.ratecategoryid5,
P1.ratecategoryid6,
P1.ratecategoryid7,
P1.ratecategoryid8,
P1.ratecategoryid9,
P1.ratecategoryid10,
P1.low,
P1.target,
P1.high,
P1.customdetails,
P1.datapoints
INTO #pivotresult
FROM (SELECT *
FROM (SELECT id,
rt1,
low,
target,
high,
Cast (ratecategoryid1 AS NVARCHAR(max)) AS
RateCategoryIDnum,
Cast (ratetableid AS BIGINT) AS CASTRDID,
customdetails,
Cast (id1 AS NVARCHAR(max)) AS DataPoints
FROM m) src
PIVOT ( Max(ratecategoryidnum)
FOR rt1 IN ([RateCategoryID1],
[RateCategoryID2],
[RateCategoryID3],
[RateCategoryID4],
[RateCategoryID5],
[RateCategoryID6],
[RateCategoryID7],
[RateCategoryID8],
[RateCategoryID9],
[RateCategoryID10]) )Piv)P1
SELECT OP.ratecategoryid1,
OP.ratecategoryid2,
OP.ratecategoryid3,
OP.ratecategoryid4,
OP.ratecategoryid5,
OP.ratecategoryid6,
OP.ratecategoryid7,
OP.ratecategoryid8,
OP.ratecategoryid9,
OP.ratecategoryid10,
OP.low,
OP.target,
OP.high,
OP.customdetails,
OP.datapoints
INTO #datatoinsert
FROM #pivotresult OP
LEFT JOIN (SELECT T.id,
T.castrdid,
T.ratecategoryid1,
T.ratecategoryid2,
T.ratecategoryid3,
T.ratecategoryid4,
T.ratecategoryid5,
T.ratecategoryid6,
T.ratecategoryid7,
T.ratecategoryid8,
T.ratecategoryid9,
T.ratecategoryid10,
T.customdetails,
T.datapoints
FROM #pivotresult T
LEFT JOIN ratecard R
ON T.castrdid = r.ratetableid
WHERE r.ratetableid = @RateTableID
AND T.ratecategoryid1 = r.ratecategoryid1
AND T.ratecategoryid2 = r.ratecategoryid2
GROUP BY T.id,
T.castrdid,
T.ratecategoryid1,
T.ratecategoryid2,
T.ratecategoryid3,
T.ratecategoryid4,
T.ratecategoryid5,
T.ratecategoryid6,
T.ratecategoryid7,
T.ratecategoryid8,
T.ratecategoryid9,
T.ratecategoryid10,
T.customdetails,
T.datapoints) M
ON OP.id = M.id
WHERE M.id IS NULL
SELECT * FROM @UpdatedRateTableList
IF(@OperationParameter=2 OR @OperationParameter=4)
BEGIN
UPDATE ratecard
SET ratecard.low = UPDATEDATA.low,
ratecard.target = UPDATEDATA.target,
ratecard.high = UPDATEDATA.high,
ratecard.customfields = UPDATEDATA.customdetails,
ratecard.datapointscount = UPDATEDATA.datapoints
FROM ratecard
INNER JOIN #datatoinsert UPDATEDATA
ON ratecard.ratetableid = @RateTableID
WHERE ( ratecard.ratecategoryid1 = UPDATEDATA.ratecategoryid1
OR ( ratecard.ratecategoryid1 IS NULL
AND UPDATEDATA.ratecategoryid1 IS NULL ) )
AND ( ratecard.ratecategoryid2 = UPDATEDATA.ratecategoryid2
OR ( ratecard.ratecategoryid2 IS NULL
AND UPDATEDATA.ratecategoryid2 IS NULL ) )
AND ( ratecard.ratecategoryid3 = UPDATEDATA.ratecategoryid3
OR ( ratecard.ratecategoryid3 IS NULL
AND UPDATEDATA.ratecategoryid3 IS NULL ) )
AND ( ratecard.ratecategoryid4 = UPDATEDATA.ratecategoryid4
OR ( ratecard.ratecategoryid4 IS NULL
AND UPDATEDATA.ratecategoryid4 IS NULL ) )
AND ( ratecard.ratecategoryid5 = UPDATEDATA.ratecategoryid5
OR ( ratecard.ratecategoryid5 IS NULL
AND UPDATEDATA.ratecategoryid5 IS NULL ) )
AND ( ratecard.ratecategoryid6 = UPDATEDATA.ratecategoryid6
OR ( ratecard.ratecategoryid6 IS NULL
AND UPDATEDATA.ratecategoryid6 IS NULL ) )
AND ( ratecard.ratecategoryid7 = UPDATEDATA.ratecategoryid7
OR ( ratecard.ratecategoryid7 IS NULL
AND UPDATEDATA.ratecategoryid7 IS NULL ) )
AND ( ratecard.ratecategoryid8 = UPDATEDATA.ratecategoryid8
OR ( ratecard.ratecategoryid8 IS NULL
AND UPDATEDATA.ratecategoryid8 IS NULL ) )
AND ( ratecard.ratecategoryid9 = UPDATEDATA.ratecategoryid9
OR ( ratecard.ratecategoryid9 IS NULL
AND UPDATEDATA.ratecategoryid9 IS NULL ) )
AND ( ratecard.ratecategoryid10 = UPDATEDATA.ratecategoryid10
OR ( ratecard.ratecategoryid10 IS NULL
AND UPDATEDATA.ratecategoryid10 IS NULL ) )
END
IF(@OperationParameter=3 OR @OperationParameter=4)
BEGIN
INSERT INTO ratecard
(RateTableId,
ratecategoryid1,
ratecategoryid2,
ratecategoryid3,
ratecategoryid4,
ratecategoryid5,
ratecategoryid6,
ratecategoryid7,
ratecategoryid8,
ratecategoryid9,
ratecategoryid10,
low,
target,
high,
customfields,
datapointscount)
SELECT @RateTableID,
ratecategoryid1,
ratecategoryid2,
ratecategoryid3,
ratecategoryid4,
ratecategoryid5,
ratecategoryid6,
ratecategoryid7,
ratecategoryid8,
ratecategoryid9,
ratecategoryid10,
low,
target,
high,
customdetails,
datapoints
FROM #datatoinsert
END
IF(@OperationParameter=1)
BEGIN
DELETE FROM RateCard WHERE RateTableId=@RateTableID
INSERT INTO ratecard
(RateTableId,
ratecategoryid1,
ratecategoryid2,
ratecategoryid3,
ratecategoryid4,
ratecategoryid5,
ratecategoryid6,
ratecategoryid7,
ratecategoryid8,
ratecategoryid9,
ratecategoryid10,
low,
target,
high,
customfields,
datapointscount)
SELECT @RateTableID,
ratecategoryid1,
ratecategoryid2,
ratecategoryid3,
ratecategoryid4,
ratecategoryid5,
ratecategoryid6,
ratecategoryid7,
ratecategoryid8,
ratecategoryid9,
ratecategoryid10,
low,
target,
high,
customdetails,
datapoints
FROM #pivotresult
END
END
i am getting
incorrect syntax near';' with statement CTE
What I have tried:
i am not able find what is wrong in this