c#动态创建存储过程中，提示'go'附近有语法错误解决方案

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› Node.js›Node.js教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

1  public int CreateDB_KillProc_proc()
2         {
3             int result = 0;
4             string SqlStr = "";
5             //组合Sql语句
6             SqlStr += @"USE [RadarDataBase]
7                     GO
8                     SET ANSI_NULLS ON
9                     GO
10                     SET QUOTED_IDENTIFIER ON
11                     GO
12                     create proc [dbo].[P_KillConnections]
13                     @dbname varchar(200)
14                     as
15                     declare @sql nvarchar(500)
16                     declare @spid nvarchar(20)
17                     declare #tb cursor for
18                     select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
19                     open #tb
20                     fetch next from #tb into @spid
21                     while @@fetch_status=0
22                     begin
23                     exec('kill '+@spid)
24                     fetch next from #tb into @spid
25                     end close #tb deallocate #tb ";
26
27             //执行Sql语句
28             try
29             {
30                 result = DbHelperSQL.ExecuteSql(SqlStr);
31             }
32             catch (Exception e)
33             {
34                 ErrStr = e.Message;
35                 return -2;
36             }
37             return result;
38         }

但执行过程中出现以下错误

以下是我在查询分析器中能正常使用的脚本代码。

1 USE [RadarDataBase]
2                     GO
3                     SET ANSI_NULLS ON
4                     GO
5                     SET QUOTED_IDENTIFIER ON
6                     GO
7                     create proc [dbo].[P_KillConnections]
8                     @dbname varchar(200)
9                     as
10                     declare @sql nvarchar(500)
11                     declare @spid nvarchar(20)
12                     declare #tb cursor for
13                     select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
14                     open #tb
15                     fetch next from #tb into @spid
16                     while @@fetch_status=0
17                     begin
18                     exec('kill '+@spid)
19                     fetch next from #tb into @spid
20                     end close #tb deallocate #tb ";

查询分析器是sql客户端，它可以识别go用来分批提交，但go不是sql语句，sql服务器不能识别所以不能用在程序中。

所以将带有GO的语句分成多条sql语句，执行多条SQL语句，实现数据库事务，代码如下：

1  public int CreateDB_KillProc_proc()
2         {
3             List<string> strSqls = new List<string>();
4             int result = 0;
5             string SqlStr = "";
6             //组合Sql语句
7             SqlStr += "USE [RadarDataBase]";
8             strSqls.Add(SqlStr);
9             SqlStr = "SET ANSI_NULLS ON ";
10             strSqls.Add(SqlStr);
11             SqlStr = "SET QUOTED_IDENTIFIER ON  ";
12             strSqls.Add(SqlStr);
13             SqlStr = @"create proc [dbo].[P_KillConnections]
14                     @dbname varchar(200)
15                     as
16                     declare @sql nvarchar(500)
17                     declare @spid nvarchar(20)
18                     declare #tb cursor for
19                     select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
20                     open #tb
21                     fetch next from #tb into @spid
22                     while @@fetch_status=0
23                     begin
24                     exec('kill '+@spid)
25                     fetch next from #tb into @spid
26                     end close #tb deallocate #tb";
27             strSqls.Add(SqlStr);
28             //执行Sql语句
29             try
30             {
31                 result = DbHelperSQL.ExecuteSqlTran(strSqls);//这个函数功能是执行多条sql语句实现数据库事务
32             }
33             catch (Exception e)
34             {
35                 ErrStr = e.Message;
36                 return -2;
37             }
38             return result;
39         }
40