We help IT Professionals succeed at work.

sql semi-colon WITH error syntax

232 Views
Last Modified: 2014-12-02
Hi, I have a problem with the following code, giving the error below.  If I add a semi-colon before 'WITH' as it says to do when I've looked up the problem, it then returns that as an error so am stuck!  Any ideas of the correct syntax?

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
    DROP TABLE #TempTable

CREATE TABLE #TempTable(
OrderBy int,
TotalUsers int,
FinancialYear int,
CombYear varchar(20),
Measure varchar(150))



INSERT INTO #TempTable (OrderBy, TotalUsers, FinancialYear, CombYear, Measure)

;With myTable as 
	(SELECT '1' AS OrderBy, count( D1.UserID) AS TotalUsers, FinYear, CombYr, 'Number of Active Accounts Created in Period' AS Name
FROM ext_User AS D1
INNER JOIN Dates
ON CONVERT(VARCHAR, UserCreated, 110) = CONVERT(VARCHAR, Date, 110)
where UserCreated < GetDate() and UserStatusCode = 'A'
GROUP BY FinYear, CombYr)

Select OrderBy, TotalUsers, FinYear, Sum(TotalUsers) OVER(ORDER BY FinYear) AS RollingTotal, CombYr, Name
from myTable

SELECT * FROM #TempTable

Open in new window



Error is:
Msg 156, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'With'.
Msg 319, Level 15, State 1, Line 15
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.



With the semi-colon code is:

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
    DROP TABLE #TempTable

CREATE TABLE #TempTable(
OrderBy int,
TotalUsers int,
FinancialYear int,
CombYear varchar(20),
Measure varchar(150))



INSERT INTO #TempTable (OrderBy, TotalUsers, FinancialYear, CombYear, Measure)

;With myTable as 
	(SELECT '1' AS OrderBy, count( D1.UserID) AS TotalUsers, FinYear, CombYr, 'Number of Active Accounts Created in Period' AS Name
FROM ext_User AS D1
INNER JOIN Dates
ON CONVERT(VARCHAR, UserCreated, 110) = CONVERT(VARCHAR, Date, 110)
where UserCreated < GetDate() and UserStatusCode = 'A'
GROUP BY FinYear, CombYr)

Select OrderBy, TotalUsers, FinYear, Sum(TotalUsers) OVER(ORDER BY FinYear) AS RollingTotal, CombYr, Name
from myTable


SELECT * FROM #TempTable

Open in new window


Error message is:
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near ';'.

Thanks in advance
Comment
Watch Question

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
The semi-colon is correct in that it has to be before any WITH construct.  I think Microsoft does that to make exam questions extra hard.

Without knowing anything about this code, try this...
;
With myTable as (
   SELECT '1' AS OrderBy, count( D1.UserID) AS TotalUsers, FinYear, CombYr, 'Number of Active Accounts Created in Period' AS Name
   FROM ext_User AS D1
      JOIN Dates ON CONVERT(VARCHAR, UserCreated, 110) = CONVERT(VARCHAR, Date, 110)
   WHERE UserCreated < GetDate() and UserStatusCode = 'A'
   GROUP BY FinYear, CombYr)
INSERT INTO #TempTable (OrderBy, TotalUsers, FinancialYear, CombYear, Measure)
SELECT OrderBy, TotalUsers, FinYear, Sum(TotalUsers) OVER(ORDER BY FinYear) AS RollingTotal, CombYr, Name
FROM myTable

Open in new window

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
The problem is that each SQL statement should/could be terminated with a semi-colon. Due to the usage of the word WITH in other T-SQL statements the parser requires that the last statement before a CTE must be terminated with a colon.
Also your usage of the CTE is incorrect:

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
    DROP TABLE #TempTable;

CREATE TABLE #TempTable
    (
      OrderBy INT ,
      TotalUsers INT ,
      FinancialYear INT ,
      CombYear VARCHAR(20) ,
      Measure VARCHAR(150)
    );

WITH    myTable
          AS ( SELECT   '1' AS OrderBy ,
                        COUNT(D1.UserID) AS TotalUsers ,
                        FinYear ,
                        CombYr ,
                        'Number of Active Accounts Created in Period' AS Name
               FROM     ext_User AS D1
                        INNER JOIN Dates ON CONVERT(VARCHAR, UserCreated, 110) = CONVERT(VARCHAR, Date, 110)
               WHERE    UserCreated < GETDATE()
                        AND UserStatusCode = 'A'
               GROUP BY FinYear ,
                        CombYr
             )
    INSERT  INTO #TempTable
            ( OrderBy ,
              TotalUsers ,
              FinancialYear ,
              CombYear ,
              Measure
            )
            SELECT  OrderBy ,
                    TotalUsers ,
                    FinYear ,
                    SUM(TotalUsers) OVER ( ORDER BY FinYear ) AS RollingTotal ,
                    CombYr ,
                    Name
            FROM    myTable;

SELECT  *
FROM    #TempTable;

Open in new window

Director, Practice Manager and Computing Consultant
Awarded 2014
Top Expert 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
@Philip, there is no statement before your CTE. Before the CTE is a GO. This is the SSME batch terminator. Thus no semi-colon is needed. This is a (:totally:) different case.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.