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?

Answer 1

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.