Getting unexpected "Incorrect Syntax" error near the word "With"

Question

So My issue is that I am getting the error "Incorrect Syntax near the keyword 'With'" I haven't really used the with command much before so I'm not too sure what the issue is relating to. This code will be used in an SSRS report and you'll see where I've put the comments that I will replace those values with parameters. Essentially the order of operation is to create a table of all the users where their branch matches the input provided. the select their "flag" which is just a unique number which allows us to calculate what permissions each user has and convert that to binary, then cross check if that user has the permissions we need and if so create a new table of users with those permissions

Declare @Users table(Names nvarchar(50) not null, Flag int)
Declare @ValidUsers table(Names nvarchar(50) not null)
Declare @Office int
Declare @NumberOfRecords int
Declare @Count int
Declare @IntCount int
DECLARE @Binary AS nvarchar(16);
declare @bit as nvarchar(1);
declare @PermissionSub as nvarchar(1);
declare @Permission as nvarchar(16);
declare @ShouldContinue as bit
set @ShouldContinue = 1;
set @Permission = '0001111111111111'; /* going to pass this value */
set @Count = '1'
set @IntCount = '1'
Set @Office = '3' /* going to pass this value */

Insert into @Users
Select dbUser.usrFullName, udFeeEarnerLicence.purchaseFlag
From udFeeEarnerLicence INNER JOIN
    dbUser ON udFeeEarnerLicence.feeUsrId = dbUser.usrID
where dbUser.brId = @Office

select @NumberOfRecords = COUNT(Flag) from @Users

DECLARE @Flag AS int;
select @Flag = Flag from @Users

while(@Count <= @NumberOfRecords)
    begin
    select @Flag = Flag from @Users where ROW_NUMBER() over (order by Flag) = @Count

    WITH A AS (
        SELECT 0 AS ORD, @Flag AS NUMBER, CAST('' AS VARCHAR(20)) AS BITS
        UNION ALL
        SELECT ORD+1, NUMBER/2,  CAST(BITS+CAST(NUMBER%2 AS VARCHAR(20)) AS VARCHAR(20))
              FROM A
              WHERE  NUMBER>0)
    SELECT @Binary = RIGHT('000000000000000'+ CASE WHEN BITS='' THEN '0' ELSE REVERSE(BITS) END,16) 
    FROM A
    WHERE NUMBER = 0;
    while(@IntCount <= 16)
        begin

        select @bit = SUBSTRING(@Binary, @IntCount, @IntCount + 1)
        select @PermissionSub = SUBSTRING(@Permission, @IntCount, @IntCount + 1)
        if(@PermissionSub = '1' and @bit != '1') /* if Permission selection is required and user does not have permission*/
            begin
            SET @ShouldContinue = 0
            break;
            end     
        end
    Set @IntCount = 0
    if(@ShouldContinue = 0)
        begin
        continue
        end
    Insert into @ValidUsers
    select Names from @Users where ROW_NUMBER() over (order by Flag) = @Count
end

I've just tried to run it again with the ; before the with and that just bring up another error saying "Windowed functions can only appear in the select or order by clauses"

Start using a `;` to end all your statements: https://sqlblog.org/2009/09/03/ladies-and-gentlemen-start-your-semi-colons — , Mar 14 '17 at 13:59
Even better than fixing the syntax error would be to eliminate these nested loops. From what is posted this entire looping mechanism could be replace with a single set based insert statement. — Sean Lange, Mar 14 '17 at 14:02
I've updated the post to include the new error that appears when I enter ';' before the with. I've not looked into single set based insert statements before so thanks for the suggestion @SeanLange Once I've got this working I will see if I can refactor it. — Jack Tyler, Mar 14 '17 at 14:13
The right way to fix this is to rewrite it. This is maintenance nightmare. And the performance of this is not going to scale at all. I would be willing to help you make this a set based solution but would need some details about the tables and what this is trying to do. — Sean Lange, Mar 14 '17 at 14:17
As far as this new error goes, you can't use `ROW_NUMBER()` in the `WHERE` clause. I'll explain more in an answer I'm going to write now. — MK_, Mar 14 '17 at 14:29
This use two tables. The first being dbUser which has the users information of which I want their full name and their unique ID. I'm joining that to udFeeEarner licence which has their user id and along side 5 different flags which are just numbers that when translated to binary give me a bit value of either 1 or 0 saying if a user has the corresponding permissions or not. I'm trying to make a report where the user can select which off and flag to use as well what options so that it only return the users with those credentials. — Jack Tyler, Mar 14 '17 at 14:32

score 1 · Accepted Answer · edited May 23 '17 at 11:53

The first error concerning the WITH clause requires you to put ; before the WITH in order to end the last statement before it.

The second error is because you're trying to use a windowing function in the WHERE clause which isn't possible. You can resolve it by changing this part of your code:

Insert into @ValidUsers
    select Names from @Users where ROW_NUMBER() over (order by Flag) = @Count

with:

; WITH CTE AS (
    SELECT Names, ROW_NUMBER() OVER (ORDER BY Flag) AS RwNr
    FROM @Users
)
INSERT INTO @ValidUsers
    SELECT Names
    FROM CTE
    WHERE RwNr = @Count

Note: This code hasn't been tested but I believe it should work even if you copy/paste it into your solution.

However, as @Sean Lange commented, you might want to refactor your whole solution here as it isn't scalable at all. My snippet of code (provided above) should just sort out your errors, but won't solve your scalability and performance issues.

EDIT: As you pointed out, there's another window function in your code before the one I already gave you a fixed snippet of code for. It's the same error basically. Change this part of your code:

select @Flag = Flag from @Users where ROW_NUMBER() over (order by Flag) = @Count

with:

; WITH CTE AS (
    SELECT Flag, ROW_NUMBER() OVER (ORDER BY Flag) AS RwNr
    FROM @Users
)
SELECT TOP(1) @Flag = Flag -- this TOP(1) is just a fail-safe
FROM CTE
WHERE RwNr = @Count

Also, here is some documentation and useful links on the subject:

why semicolon when using CTE, a nice reasonable explanation: https://stackoverflow.com/a/6938089/6492765
CTE official documentation: https://technet.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
why no window functions in WHERE clauses, right here on StackOverflow: https://stackoverflow.com/a/13997396/6492765
window ranking functions official documentation: https://msdn.microsoft.com/en-us/library/ms189798.aspx

In the error message it says it occurs on line 31 which is "select \@Flag = Flag from @Users where ROW_NUMBER() over (order by Flag) = \@Count;" just before the "with" although the error might also appear later on in which case I believe your code would work. — Jack Tyler, Mar 14 '17 at 14:41
Oh, I didn't see you have another window function there. I'll expand my answer with the fix to that line, but it would've given you another error for the part where I already fixed you up a code snippet. — MK_, Mar 14 '17 at 14:43
Thanks you it appears no more errors have appeared. with the edit I had to add "FROM CTE" between the select and where. I see what you mean as well about the performance issues it's been running for a while now and still no result. I'll have a look into set based solutions and what they can do for me. — Jack Tyler, Mar 14 '17 at 14:55
Oh, of course you need a `FROM`, I accidentally glossed over it. I added it just now. Nevertheless, you should definitely look into making it a set-based solution instead of iterating with `WHILE`, but I'm glad at least your code is now error-free even if it is not the best performing one. — MK_, Mar 14 '17 at 15:01
Thanks for marking it as an answer. I also added some useful links just for FYI or for someone reading it up. Good luck! — MK_, Mar 14 '17 at 15:07

score 0 · Answer 2 · answered Mar 14 '17 at 13:55

0

Add a ; after the with.

This will remove the error.

answered Mar 14 '17 at 13:55

asktonishant

183
13

2

The `;` needs to end the statement _before_ the with. – Mar 14 '17 at 14:06

score 0 · Answer 3 · answered Mar 14 '17 at 14:20

SQL statements should be terminated with semi-colons.

Although not fully mandatory yet, many newer SQL constructs require the previous statement to be terminated. CTEs included

From https://msdn.microsoft.com/en-us/library/ms177563.aspx (and indeed older versions https://msdn.microsoft.com/en-us/library/ms177563(v=sql.100).aspx)

Transact-SQL statement terminator. Although the semicolon is not required for most statements in this version of SQL Server, it will be required in a future version.

Getting unexpected "Incorrect Syntax" error near the word "With"

3 Answers3