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 '[email protected]_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 = @[email protected]_temp  
	fetch next from @Cursor_Name into @Columns_temp   
   End
Else
   While (len(@columns) <= @length_limit) and (@@FETCH_STATUS = 0)  Begin
	set @Columns = @[email protected]+'.'[email protected]_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 '[email protected]_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 = @[email protected]  
    	fetch next from CC into @colComma   
       End
    Else
       While (len(@columns) <= @length_limit) and (@@FETCH_STATUS = 0)  Begin
    	set @Columns = @[email protected]+'.'[email protected]    
    	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 New member
    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.