SQL Server和sqsh关键字'ORDER‘附近的语法不正确

Question

我正在使用sqsh执行一个存储过程：

为了让你更容易阅读，我简化了如下步骤：

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

我得到的错误是：

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'.

这是非半模式化的版本：

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

我已经看过其他类似的问题了。我找不到任何语法错误，还能是什么呢？SQLServer版本？

Answer 1

显然，这是相当古老的，但如果有人在未来发现这一点，这里是解决方案。

sqsh使用命令\go来分解批处理。sqsh还将分号本身用作一种内联\go命令的“关键字”。因此，从本质上讲，您的过程被分为两个无意义的批处理：

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

不幸的是，如果您删除分号，SQL Server将会报错，因此您需要禁用分号批处理分隔功能。

从手册页：

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.

这是一个可以从shell设置的变量，也可以通过-L选项从原始sqsh命令设置变量：-Lsemicolon_hack=false。

你实际上有第二个错误，抱怨GO的用法。如果使用GO，sqsh将抛出错误。您需要以任何一种方式提交批处理，否则sqsh实际上什么也不做，因此将该GO更改为\go。因此，最终的sql文件应该如下所示：

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

注意:您已经这样做了，但是\go命令的前面必须始终有一个新行。