SQL Server 中的游标和更新

发布于 2024-12-16 19:12:34 字数 683 浏览 1 评论 0原文

当我尝试在 SQL Server 2005 中执行此代码时，收到一条错误消息：

消息 156，级别 15，状态 1，第 20 行
关键字“close”附近的语法不正确。
消息 102，第 15 级，状态 1，第 21 行
“dbname”附近的语法不正确。

我的代码有什么问题吗？

DECLARE @name nvarchar(max), @stat nvarchar(max)   

set @stat = N'update DBNAME.dbo.Ad 
set Label = ''Special Ad'' where Label =''AdXXXX'''

DECLARE dbname CURSOR FOR select name from sys.databases where name like '%config%'

open dbname   
begin try
    while 1=1 
    begin
      fetch next from dbname into @name
      set @stat=REPLACE(@stat,'DBNAME',@name)
      exec sp_executesql @stat
    end  
end try

close dbname   
deallocate dbname

原文

I'm getting an error message when I'm trying to execute this code in SQL Server 2005:

Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'close'.
Msg 102, Level 15, State 1, Line 21
Incorrect syntax near 'dbname'.

What is wrong with my code?

DECLARE @name nvarchar(max), @stat nvarchar(max)   

set @stat = N'update DBNAME.dbo.Ad 
set Label = ''Special Ad'' where Label =''AdXXXX'''

DECLARE dbname CURSOR FOR select name from sys.databases where name like '%config%'

open dbname   
begin try
    while 1=1 
    begin
      fetch next from dbname into @name
      set @stat=REPLACE(@stat,'DBNAME',@name)
      exec sp_executesql @stat
    end  
end try

close dbname   
deallocate dbname

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

素手挽清风 2024-12-23 19:12:34

TRY 块后面必须紧跟着 CATCH 块：

BEGIN TRY
    -- some statements here
END TRY
BEGIN CATCH
    -- other statements here
END CATCH

A TRY block must be immediately followed by a CATCH block:

BEGIN TRY
    -- some statements here
END TRY
BEGIN CATCH
    -- other statements here
END CATCH

回复收藏 0 原文

我最亲爱的 2024-12-23 19:12:34

请使用以下 sql 游标脚本

DECLARE @name nvarchar(max), @stat nvarchar(max)

DECLARE dbname CURSOR FOR select name from sys.databases --where name like '%config%'  
open dbname    

FETCH NEXT FROM dbname INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN

    set @stat = N'update DBNAME.dbo.Ad  set Label = ''Special Ad'' where Label =''AdXXXX'''  
    set @stat=REPLACE(@stat,'DBNAME',@name)       
    exec sp_executesql @stat     

    FETCH NEXT FROM dbname INTO @name   
END

close dbname    
deallocate dbname

请注意，set @stat 声明语句已移至光标
因为在第一次替换后，原始的 @stat 发生了变化，并且您将无法再次更改它

Please use the following sql cursor script

DECLARE @name nvarchar(max), @stat nvarchar(max)

DECLARE dbname CURSOR FOR select name from sys.databases --where name like '%config%'  
open dbname    

FETCH NEXT FROM dbname INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN

    set @stat = N'update DBNAME.dbo.Ad  set Label = ''Special Ad'' where Label =''AdXXXX'''  
    set @stat=REPLACE(@stat,'DBNAME',@name)       
    exec sp_executesql @stat     

    FETCH NEXT FROM dbname INTO @name   
END

close dbname    
deallocate dbname

Note that the set @stat declaration statement is moved into the cursor
Because after the first replace the original @stat changes and you'll not be able to alter it again

回复收藏 0 原文

旧瑾黎汐 2024-12-23 19:12:34

您有一个 TRY 块，但没有 CATCH 块：

...
open dbname   
begin try
  while 1=1 begin
      fetch next from dbname into @name
      set @stat=REPLACE(@stat,'DBNAME',@name)
      exec sp_executesql @stat

  end  
end try
begin catch
    <..some error handling code>
end catch
...

请参阅 尝试...捕获

you have a TRY block without a CATCH block:

...
open dbname   
begin try
  while 1=1 begin
      fetch next from dbname into @name
      set @stat=REPLACE(@stat,'DBNAME',@name)
      exec sp_executesql @stat

  end  
end try
begin catch
    <..some error handling code>
end catch
...

See the documentation on TRY...CATCH

回复收藏 0 原文

~没有更多了~