这个存储过程中有什么语法错误 [英] What syntax wrong in this stored procdure
本文介绍了这个存储过程中有什么语法错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
USE [OMA]
GO
/****** Object: StoredProcedure [dbo].[UpdateRateTable] Script Date: 6/9/2016 2:56:21 PM ******/
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
--Insert To Rate Table
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
--Inserting To Temp table
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
--UPDATE TO RATECARD
IF(@OperationParameter=2 OR @OperationParameter=4)
BEGIN
----Update ratecard table Begins
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 Of Update ratecard table
END
---Insert TO RateCard
IF(@OperationParameter=3 OR @OperationParameter=4)
BEGIN
--Inserting to ratecard table Begins
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 Of Insert to RateCard
END
--REPLACE RATECARD
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得到附近的语法不正确;'声明CTE
我尝试过：
i无法找到错误的
i am getting incorrect syntax near';' with statement CTE
What I have tried:
i am not able find what is wrong in this
推荐答案
好像，问题已经解决了......
Seems, the issue has been solved...
PIEBALDconsult [ ^ ]写道：
INSERT INTO [dbo]。[TempRateCardDetails ]
太早了，就像往常一样把它放在 SELECT 之前，即使有CTE也是如此。
The
INSERT INTO [dbo].[TempRateCardDetails]
is too early, put it just before the SELECT as always, even when there's a CTE.
已回复以从未答复的列表中删除问题。
这篇关于这个存储过程中有什么语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
