How do I fetch multiple columns for use in a cursor loop?

Question

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''' ``` I get the following message, 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. Edit 1 - 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_rena…

Accepted Answer

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
```

How do I fetch multiple columns for use in a cursor loop?

Solution

Problem