Incorrect marking of unused cursor variable

BodhiBodhi Posts: 7
edited January 4, 2007 12:49PM in SQL Refactor Previous Versions
The following procedure is poor code because it is better to declare a cursor without using a separately declared cursor variable. But, it does compile and execute correctly. The problem is that "Find Unused Variables and Parameters" marks @cursor_name as unused.
CREATE proc p_common_columns_list @other_schema sysname, @table_name sysname
, @Context varchar(4) = '', @length_limit int = 7000
, @columns varchar(8000) output
AS
-- SEE Also: dbo.WhatColumns()

--Passes back the column names that are in a table in both schemas
--in the correct order, separated by commas.
--Can be given a context.
--Sample output if a context of 'sp' is given.
--sp.Column1, sp.Column2, sp.Column3
--Sample output if a context is ''.
--Column1, Column2, Column3

-- SEE ALSO: dbo.safe_columns_list

--Copyright 2000, 2001, 2002 GetronicsWang Co., LLC. All rights reserved.
--18Jun02 Bodhi - as procedure
--03Dec02 - Use syscolumns instead of information_schema.columns to support catalog tables.

set nocount on
declare @Columns_temp as sysname
declare @Cursor_Name as cursor
declare @id int 
create table #t (column_name sysname not null)
set @id = object_id(@table_name)
If @id is null Begin
	set @Columns = 'Invalid object Name '+@table_Name
	drop table #t
	return -1
End
insert into #t exec dbo.list_common_columns @other_schema, @table_name 
	
set @Cursor_Name = cursor FAST_FORWARD for
select C.Name+', ' from dbo.syscolumns C
where id = @id
and (not Type = 34) and (not Type = 35) -- data_Type not in ('text', 'ntext', 'image') -- not useful in unions
  and exists (select 1 from #t where #t.column_Name = C.Name)
order by colid

set @Columns = ''
open @Cursor_Name
fetch next from @Cursor_Name into @Columns_temp

--I used multiple While statements in the interest of speed
If (@Context = '') 
   While (len(@columns) <= @length_limit) and (@@FETCH_STATUS = 0) Begin
	set @Columns = @Columns+@Columns_temp  
	fetch next from @Cursor_Name into @Columns_temp   
   End
Else
   While (len(@columns) <= @length_limit) and (@@FETCH_STATUS = 0)  Begin
	set @Columns = @Columns+@Context+'.'+@Columns_temp    
	fetch next from @Cursor_Name into @Columns_temp   
   End

Deallocate @Cursor_Name
drop table #t
if @columns > ''
	set @columns = left(@Columns,len(@Columns)-1)
return 0

go
if 1 is null begin
	declare @clist varchar(4000)
	exec dbo.p_common_columns_list 'Globalfs_Archive','dtproperties', '', 400, @columns =  @clist out
	print @clist
end
go

Comments

  • Here is the same procedure slightlty refactored. Now @colComma is marked as unused in the "fetch next" statements.
    CREATE proc p_common_columns_list @other_schema sysname, @table_name sysname
    , @Context varchar(4) = '', @length_limit int = 7000
    , @columns varchar(8000) output
    AS
    --Copyright 2000, 2001, 2002, 2007 GetronicsWang Co., LLC. All rights reserved.
    --18Jun02 Bodhi - as procedure
    --03Dec02 - Use syscolumns instead of information_schema.columns to support catalog tables.
    --03Jan07 - Refactored for simplicity.
    set nocount on
    create table #t (column_name sysname not null)
    declare @colComma as sysname
    declare @id int 
    set @id = object_id(@table_name)
    If @id is null Begin
    	set @Columns = 'Invalid object Name '+@table_Name
    	drop table #t
    	return -1
    End
    insert into #t exec dbo.list_common_columns @other_schema, @table_name 
    	
    DECLARE CC cursor local FAST_FORWARD for
    select C.Name+', ' from dbo.syscolumns C
    where id = @id
    and (not Type = 34) and (not Type = 35) -- data_Type not in ('text', 'ntext', 'image') -- not useful in unions
      and exists (select 1 from #t where #t.column_Name = C.Name)
    order by colid
    
    set @Columns = ''
    open CC
    fetch from CC into @colComma
    
    --I used multiple While statements in the interest of speed
    If (@Context = '') 
       While (len(@columns) <= @length_limit) and (@@FETCH_STATUS = 0) Begin
    	set @Columns = @Columns+@colComma  
    	fetch next from CC into @colComma   
       End
    Else
       While (len(@columns) <= @length_limit) and (@@FETCH_STATUS = 0)  Begin
    	set @Columns = @Columns+@Context+'.'+@colComma    
    	fetch next from CC into @colComma   
       End
    
    Deallocate CC
    drop table #t
    if @columns > ''
    	set @columns = left(@Columns,len(@Columns)-1)
    return 0
    go
    if 1 is null begin
    	declare @clist varchar(4000)
    	exec dbo.p_common_columns_list 'DBMASTER.mydb','xxi_tables', '', 400, @columns =  @clist out
    	print @clist
    end
    go
    
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello,

    I'll admit there are some known issues in the 'find unused variables' function of refactor, especially when it comes to code containing cursors. This will be addressed in a future version.
Sign In or Register to comment.