针对多个数据库创建存储过程的查询意外失败[重复]

This question already has an answer here:

这个问题在这里已有答案：

• Incorrect syntax near 'GO' 5 answers
• “GO”5答案附近的语法不正确

I am trying to run a SQL query to create a stored procedure in several databases.

我试图运行SQL查询以在几个数据库中创建存储过程。

I am running the following query:

我正在运行以下查询：

DECLARE @sqlStmt nvarchar(max)

select name from sys.databases where name not in ('master', 'tempdb', 'model', 'msdb')

DECLARE dbCursor CURSOR
FOR select name from sys.databases where name not in ('master', 'tempdb', 'model', 'msdb')

DECLARE @dbname varchar(max)

OPEN dbCursor

fetch next from dbCursor into @dbname

while @@FETCH_STATUS = 0
BEGIN
    declare @stmt nvarchar(max)
    SET @stmt = 'USE ' + @dbname + ';';
    EXECUTE sp_executesql @stmt

    declare @correctTableExists bit

    set @correctTableExists = (select case when Exists(SELECT TABLE_SCHEMA + '.' + TABLE_NAME, *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME in ('MasterSchedules', 'Client'))
    THEN 1
    ELSE 0
    END)

if @correctTableExists = 1
    BEGIN TRY

        set @stmt = 'USE ' + @dbName + '

GO

CREATE PROCEDURE spGetMasterScheduleByID 
    @masterScheduleID int
AS 
BEGIN 
SELECT * FROM MasterSchedules 
WHERE MasterScheduleID = @masterScheduleID 
END'
        EXECUTE sp_executesql @stmt
    END TRY
    BEGIN CATCH
        SELECT   
            ERROR_NUMBER() AS ErrorNumber  
            ,ERROR_MESSAGE() AS ErrorMessage;
        print @stmt + '
        Failed for ' + @dbName
    END CATCH

FETCH NEXT FROM dbCursor
INTO @dbname
END
CLOSE dbCursor;
DEALLOCATE dbCursor;

However, I get the following error message:

但是，我收到以下错误消息：

Error 102
Incorrect syntax near 'GO'.

When I look at the Messages, I see the following message for every database name I'm running this against:

当我查看消息时，我看到以下每个数据库名称的消息：

(1 row(s) affected)
USE [Database Name]

GO

CREATE PROCEDURE spGetMasterScheduleByID 
    @masterScheduleID int
AS 
BEGIN 
    SELECT * FROM MasterSchedules 
    WHERE MasterScheduleID = @masterScheduleID 
END
            Failed for [Database Name]

Why does SQL Server think that this is "incorrect syntax" given that it's clearly perfectly valid syntax? When I copy and paste that exact query into SSMS the query works perfectly.

为什么SQL Server认为这是“不正确的语法”，因为它显然是完全有效的语法？当我将该确切查询复制并粘贴到SSMS中时，查询工作正常。

I already tried running the query in several ways, such as

我已经尝试过以多种方式运行查询，例如

exec @stmt
execute @stmt
execute (@stmt)
EXECUTE sp_executesql @stmt

1 个解决方案

#1

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

GO不是Transact-SQL语句;它是sqlcmd和osql实用程序以及SQL Server Management Studio代码编辑器可识别的命令。

https://msdn.microsoft.com/en-us/library/ms188037.aspx

https://msdn.microsoft.com/en-us/library/ms188037.aspx