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来分解批处理。CCD_ 3也使用分号本身作为"0"；关键字"；作为一种内联CCD_ 4命令。因此，从本质上讲，您的程序分为两个无意义的批次：

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.

这是一个变量，您可以从sqshshell设置，也可以通过-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命令的前面必须始终有一行新行。