What syntax wrong in this stored procdure

Question

1.00/5 (1 vote)

See more:

SQL

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 am getting incorrect syntax near';' with statement CTE

What I have tried:

i am not able find what is wrong in this

Posted 9-Jun-16 17:59pm

murkalkiran

Updated 9-Jun-16 22:31pm

PIEBALDconsult

v3

Add a Solution

Comments

barneyman 10-Jun-16 0:20am

there's only one semicolon in that procedure!

and it shouldn't be there

PIEBALDconsult 10-Jun-16 0:32am

The
INSERT INTO [dbo].[TempRateCardDetails]
is too early, put it just before the SELECT as always, even when there's a CTE.

murkalkiran 10-Jun-16 0:47am

Thank you Brother it worked for me +5 from me

PIEBALDconsult 10-Jun-16 1:50am

Glad to be of service.
I've been doing WITH...INSERT...SELECT all week myself. And likely all weekend as well.

Maciej Los 10-Jun-16 1:50am

Sounds like an answer ;)

PIEBALDconsult 10-Jun-16 1:51am

Yes, but tequila speaks louder.

Maciej Los 10-Jun-16 1:57am

Put tequila away. Take vodka ;)
Cheers!

PIEBALDconsult 10-Jun-16 2:21am

In Russia, vodka puts _you_ away.
(I can't drink vodka.)

OriginalGriff 10-Jun-16 3:10am

I'd suggest you post as solution, since it fixed his problem!

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2016-06-09T22:31:00

Solution 1

Seems, the issue has been solved...

PIEBALDconsult[^] wrote:
The
INSERT INTO [dbo].[TempRateCardDetails]
is too early, put it just before the SELECT as always, even when there's a CTE.

Answered to remove question from unanswered list.

Posted 9-Jun-16 22:31pm

Maciej Los