SQL Server and sqsh Incorrect syntax near the keyword 'ORDER'

Question

I'm using sqsh to execute a stored procedure:

I simplified the procedure like this to be easyer for you to read:

CREATE PROCEDURE MyProc
    
AS
BEGIN
            SELECT *
            FROM Keys
            WHERE 1=1
            ;WITH CTE AS
            (
                SELECT TOP 1 *
                FROM MyTableName
                ORDER BY CreationTime ASC
            )
            SELECT *
            FROM CTE
END
;
go

The error I get is this:

Use: \go [-d display] [-h] [-f] [-n] [-p] [-m mode] [-s sec]
          [-t [filter]] [-w width] [-x [xgeom]] [-T title] [xacts]
     -d display  When used with -x, send result to named display
     -h          Suppress headers
     -f          Suppress footers
     -n          Do not expand variables
     -p          Report runtime statistics
     -m mode     Switch display mode for result set
     -s sec      Sleep sec seconds between transactions
     -t [filter] Filter SQL through program
                 Optional filter value overrides default variable $filter_prog
     -w width    Override value of $width
     -x [xgeom]  Send result set to a XWin output window
                 Optional xgeom value overrides default variable $xgeom
     -T title    Used in conjunction with -x to set window title
     xacts       Repeat batch xacts times
Msg 156, Level 15, State 1
Server 'myServer', Procedure 'MyProc', Line xx
Incorrect syntax near the keyword 'ORDER'.

This is the not semplified version:

CREATE PROCEDURE [dbo].[MyCustomProcedure]
    @BatchSize INT  WITH ENCRYPTION
AS
BEGIN
            DECLARE @CurrentName varchar(max)
            SET @CurrentName = [dbo].[GetCurrentyName]()
            DECLARE @CurrentID INT
            SELECT @CurrentID=ID FROM [dbo].[Keys] WHERE OriginalName = @CurrentName

            ;WITH CTE AS
            (
                SELECT TOP (@BatchSize) *
                FROM [dbo].[SerialNumbers]
                WHERE ID <> @CurrentID
                ORDER BY CreationTime ASC
            )
            UPDATE CTE
                SET EncodedSerial = (Encode(Key_GUID(@CurrentName), CONVERT(VARCHAR,(Decode(EncodedSerial))))),
                ID=@CurrentID
END
;

go

I've already looked at other similar questions. I can't find anything wrong with the syntax, what else could it be? SQLServer version?

Is your actual table name (`MyTable`) a SQL Server reserved word? Similarly, do you get the error if you run the procedure directly in SQL Server? — Aaron Dietz, Jul 24 '18 at 16:35
No, `MyTable` is not the actual table name. I changed it for clarity Unfortunately right now I can't try it directly in SQL Server — ilbonte, Jul 24 '18 at 16:49
You have simplified this so much you have removed the error. What you posted works perfectly fine. Perhaps you should share the actual code? — Sean Lange, Jul 24 '18 at 16:50
I think we'd need to see your actual code. Change the names, but leave the structure. This query seems to have been extremely simplified, and it's not really doing anything. — Shawn, Jul 24 '18 at 16:58
Also, I don't think you need the `;` between `END` and `GO`. — Shawn, Jul 24 '18 at 16:59
Just copy & paste the select in SSMS, replace variables, run it and see if it fails? — LukStorms, Jul 24 '18 at 17:05
I don't think the error is there because replacing it with a `select * from CTE` I still get the same error — ilbonte, Jul 24 '18 at 17:13
The error could be in the code of `[dbo].[GetCurrentyName]`. — Tab Alleman, Jul 24 '18 at 17:15
You can't have an `ORDER BY` in the CTE. Use `ROW_NUMBER()` and a subselect to get the number you need. — Shawn, Jul 24 '18 at 17:21
Anyway, I found the update of the CTE odd at first. But that trick works after all. [simple test here](http://rextester.com/WZFG92568) — LukStorms, Jul 24 '18 at 17:26
@LukStorms Updating a CTE is an easy way to make sure you're updating only a specific set of records. It's a pretty cool trick. :-) — Shawn, Jul 24 '18 at 17:28
I was wrong about the `ORDER BY` when used with `TOP x`. But the error still seems to indicate the problem was around the `ORDER BY`. Is there any chance that your `SELECT FROM keys` can return `NULL`? Try changing it to `SELECT @CurrentID=COALESCE(ID,0) FROM .....`. Also, you may get unexpected results if you just use `CONVERT(VARCHAR,x)` without specifying a length. Go with `CONVERT(VARCHAR(nnnnn),x)` instead. — Shawn, Jul 24 '18 at 17:43

score 0 · Answer 1 · answered Aug 19 '20 at 16:17

Obviously this is quite old, but in case anyone finds this in the future here is the solution.

sqsh uses the command \go to break batches up. sqsh also uses the semi-colon itself as a "keyword" as a sort of in-line \go command. So essentially, your procedure is split into 2 non-sense batches:

CREATE PROCEDURE [dbo].[MyCustomProcedure]
    @BatchSize INT  WITH ENCRYPTION
AS
BEGIN
            DECLARE @CurrentName varchar(max)
            SET @CurrentName = [dbo].[GetCurrentyName]()
            DECLARE @CurrentID INT
            SELECT @CurrentID=ID FROM [dbo].[Keys] WHERE OriginalName = @CurrentName

and

WITH CTE AS
            (
                SELECT TOP (@BatchSize) *
                FROM [dbo].[SerialNumbers]
                WHERE ID <> @CurrentID
                ORDER BY CreationTime ASC
            )
            UPDATE CTE
                SET EncodedSerial = (Encode(Key_GUID(@CurrentName), CONVERT(VARCHAR,(Decode(EncodedSerial))))),
                ID=@CurrentID
END

Unfortunately, SQL Server is going to complain if you remove the semi-colon, so you need to disable the semi-colon batch separation function.

From the man page:

semicolon_hack (boolean)
           Toggles on the ability to use a ';' as an in-line command terminator. This feature is
           not recommended and is only in here because enough users complained.  See section
           COMMANDS, In-Line Go.

This is a variable you can either set from the sqsh shell or from the original sqsh command via the -L option to set variable: -Lsemicolon_hack=false.

You actually have a second error there complaining about the usage of GO. sqsh will throw an error if you use GO. You will need to submit the batch either way or sqsh will literally do nothing, so change that GO to \go. So the final sql file should look like this:

CREATE PROCEDURE [dbo].[MyCustomProcedure]
    @BatchSize INT  WITH ENCRYPTION
AS
BEGIN
            DECLARE @CurrentName varchar(max)
            SET @CurrentName = [dbo].[GetCurrentyName]()
            DECLARE @CurrentID INT
            SELECT @CurrentID=ID FROM [dbo].[Keys] WHERE OriginalName = @CurrentName

            ;WITH CTE AS
            (
                SELECT TOP (@BatchSize) *
                FROM [dbo].[SerialNumbers]
                WHERE ID <> @CurrentID
                ORDER BY CreationTime ASC
            )
            UPDATE CTE
                SET EncodedSerial = (Encode(Key_GUID(@CurrentName), CONVERT(VARCHAR,(Decode(EncodedSerial))))),
                ID=@CurrentID
END

\go

Note: You already did this, but the \go command must always be preceded by a new line.

SQL Server and sqsh Incorrect syntax near the keyword 'ORDER'

1 Answers1