如何获取多个列以在游标循环中使用? [英] How do I fetch multiple columns for use in a cursor loop?
问题描述
当我尝试在游标循环中运行以下 SQL 片段时,
When I try to run the following SQL snippet inside a cursor loop,
set @cmd = N'exec sp_rename ' + @test + N',' +
RIGHT(@test,LEN(@test)-3) + '_Pct' + N',''COLUMN'''
我收到以下消息,
消息 15248,级别 11,状态 1,过程 sp_rename,第 213 行
要么参数 @objname
不明确,要么声明的 @objtype
(COLUMN) 是错误的.
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 213
Either the parameter@objname
is ambiguous or the claimed@objtype
(COLUMN) is wrong.
出了什么问题,我该如何解决?我尝试将列名括在方括号 []
中,并像某些搜索结果建议的那样用双引号 ""
括起来.
What is wrong and how do I fix it ? I tried wrapping the column name in brackets []
, and double quotes ""
like some of the search results suggested.
编辑 1 -
这是整个脚本.如何将表名传递给重命名 sp ?我不知道该怎么做,因为列名在许多表之一中.
Here is the entire script. How do I pass the table name to the rename sp ? I'm not sure how to do that since the column names are in one of many tables.
BEGIN TRANSACTION
declare @cnt int
declare @test nvarchar(128)
declare @cmd nvarchar(500)
declare Tests cursor for
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE 'pct%' AND TABLE_NAME LIKE 'TestData%'
open Tests
fetch next from Tests into @test
while @@fetch_status = 0
BEGIN
set @cmd = N'exec sp_rename ' + @test + N',' + RIGHT(@test,LEN(@test)-3) + '_Pct' + N', column'
print @cmd
EXEC sp_executeSQL @cmd
fetch next from Tests into @test
END
close Tests
deallocate Tests
ROLLBACK TRANSACTION
--COMMIT TRANSACTION
编辑 2 -该脚本旨在重命名名称与模式匹配的列,在本例中使用pct"前缀.列出现在数据库中的各种表中.所有表名都以TestData"为前缀.
Edit 2 - The script is designed to rename columns whose names match a pattern, in this case with a "pct" prefix. The columns occur in a variety of tables within the database. All table names are prefixed with "TestData".
推荐答案
这里是稍微修改的版本.更改会在代码注释中注明.
Here is slightly modified version. Changes are noted as code commentary.
BEGIN TRANSACTION
declare @cnt int
declare @test nvarchar(128)
-- variable to hold table name
declare @tableName nvarchar(255)
declare @cmd nvarchar(500)
-- local means the cursor name is private to this code
-- fast_forward enables some speed optimizations
declare Tests cursor local fast_forward for
SELECT COLUMN_NAME, TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE 'pct%'
AND TABLE_NAME LIKE 'TestData%'
open Tests
-- Instead of fetching twice, I rather set up no-exit loop
while 1 = 1
BEGIN
-- And then fetch
fetch next from Tests into @test, @tableName
-- And then, if no row is fetched, exit the loop
if @@fetch_status <> 0
begin
break
end
-- Quotename is needed if you ever use special characters
-- in table/column names. Spaces, reserved words etc.
-- Other changes add apostrophes at right places.
set @cmd = N'exec sp_rename '''
+ quotename(@tableName)
+ '.'
+ quotename(@test)
+ N''','''
+ RIGHT(@test,LEN(@test)-3)
+ '_Pct'''
+ N', ''column'''
print @cmd
EXEC sp_executeSQL @cmd
END
close Tests
deallocate Tests
ROLLBACK TRANSACTION
--COMMIT TRANSACTION
这篇关于如何获取多个列以在游标循环中使用?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!