Incorrect marking of unused cursor variable

Bodhi

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

Bodhi

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 Donahue

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.