Object_Name(@Object) fails in FROM clause

Question

Object_Name(@Object) fails in FROM clause

Jon Shearer

Grasshopper

Points: 14
More actions
May 20, 2006 at 2:13 am

#115208

I have the following proc.  select statements on lines 49-51 fail with this msg:
Server: Msg 208, Level 16, State 1, Line 49
Invalid object name 'Object_Name'.
Line 49 is "select @pendT = count(*) from Object_Name(@Object)"
I know Object_Name(@Object) is good because I put in a Print statement. It printed a valid table name.
Any ideas? I stumped.

use DBName
Declare @Object Int
Declare @Column VarChar(50)
Declare @Qry  NVarChar(500)
Declare @TblCtr int
Declare @pendT float(1)
Declare @pendN float(1)
Declare @pendY float(1)
Declare @pcntY float
Declare @pcntN float
Declare @Fetch int
Declare @Table varchar(100)
select @TblCtr = 1
Declare Table_Cursor Cursor For
Select Id, Name From SysObjects
Where XType = 'U'
Open Table_Cursor
Fetch Next From Table_Cursor
Into @Object, @Table
While @@Fetch_Status = 0 and @TblCtr < 6
Begin
Declare Column_Cursor Cursor For
Select SysColumns.Name
From SysColumns,SysTypes
Where SysColumns.Id = @Object
And SysColumns.XType = Systypes.XType
And Systypes.Name In ('char', 'nchar', 'nvarchar', 'varchar')
Open Column_Cursor
Fetch Next From Column_Cursor Into @Column
set @Fetch = @@Fetch_Status
While @Fetch = 0
Begin
  if @Column = 'pend_ind'
  begin
   Declare @Tbl varchar(100)
   select @pendT = count(*) from Object_Name(@Object)
   select @pendN = count(*) from Object_Name(@Object) where @Column = 'N'
   select @pendY = count(*) from Object_Name(@Object) where @Column = 'Y'
   select @pcntY = @pendY / @pendT, @pcntN = @pendN / @pendT
   Print 'Stats for ' + Object_Name(@Object)
   Print ' Total records = ' + Cast(Cast(@pendT as int) as varchar(100))
   Print ' Total pend=N records = ' + Cast(cast(@pendN as int) as varchar(100))
   Print ' Total pend=Y records = ' + Cast(cast(@pendY as int) as varchar(100))
   Print ' Percent pend=N records = ' + Cast(Round(@pcntN * 100.0,1) as varchar(100)) + '%'
   Print ' Percent pend=Y records = ' + Cast(Round(@pcntY * 100.0,1) as varchar(100)) + '%'
   Print ' Increase in pend_ind=Y records would be ' + Cast(@pendY * 3 as varchar(100))
   Print ' Percent increase in table size would be ' + Cast((@pendY * 4)/@pendT as varchar(100))
   Print ' '
   select @Fetch = 99
  end
  else
  begin
   Fetch Next From Column_Cursor Into @Column
   select @Fetch = @@Fetch_Status
  end
End
Close Column_Cursor
DeAllocate Column_Cursor
Fetch Next From Table_Cursor Into @Object, @Table
set @TblCtr = @TblCtr + 1
End
Close Table_Cursor
DeAllocate Table_Cursor

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Edwin Frydendall SSCrazy Points: 2361 More actions · Answer 1

Object_Name() returns a character string. The From clause requires an object.

Let's say that @Object = MemberTable. If you evaluate Object_Name(MemberTable) and stick it into your select statement, you get:

select @pendT = count(*) from 'MemberTable'

Jon Shearer Grasshopper Points: 14 More actions · Answer 2

Thanks Edwin for the feedback.

I'm not sure what you mean by 'evaluate' Object_Name(@Object).

Can you clarify for me?

Thanks

[KH] SSCommitted Points: 1908 More actions · Answer 3

you can't do this.

select @pendT = count(*) from Object_Name(@Object)

you have to use Dynamic SQL. Check out sp_executesql

Edwin Frydendall SSCrazy Points: 2361 More actions · Answer 4

Jon,

Evaluate means to process the Object_Name() function and get its result.

Ajit-297150 Mr or Mrs. 500 Points: 593 More actions · Answer 5

As suggested by [KH], use dynamic query.

Decalre @strSQL varchar(100)

set @strSQL = 'select @pendT = count(*) from ' + Object_Name(@Object)

sp_executesql @strSQL

Check this out.