Sorry, I guess I should have replied to all.
The original response I gave was incorrect, and didn't work.
But the following does execute without error.
The reason for this is you can't embed a variable into a simple SQL
statement like that, unless you're creating dynamic SQL within the where
clause. You have to build the statement and issue an EXEC statement if
there are going to be variables in the From clause.


DECLARE @TABLENAME varchar(50)
set @TABLENAME = 'sysobjects'
BEGIN
select @TABLENAME = quotename(@TABLENAME, '[]')
DECLARE @CMD VARCHAR(100)
SELECT @CMD = ' select count(*) from '+@TABLENAME+' '
EXEC (@CMD)
END


Simon


-----Original Message-----
From: thomas.[Email Address Removed] [mailto:thomas.[Email Address Removed]
Sent: Tuesday, September 17, 2002 1:05 PM
To: LazyDBA.com Discussion
Subject: RE: Inserting record count into a variable using variable
table n ame


Sang,

Sorry for the delay, I didn't have much time to play with it.
It's kind of roundabout but you might try something like this:


declare @tn varchar(20), @ls nvarchar(200), @rc int
set @tn='sysobjects'

exec ( 'select count(*) as rc into #tom from ' + @tn)
select @rc = rc from #tom

select @rc

. . . Tom

On 09/17/2002 10:43:47 AM sang.nahm wrote:
> Simon,
>
> Thanks.
>
> I am still getting the following message:
>
> Must declare the variable '@table_name'.
>
> Here are the exact codes:
>
> declare @table_name varchar(20),
> @count INT
>
> set @table_name='ORD_TABLES'
> set @count = (select count(*) from @table_name)
>
> Sang Nahm
>
> -----Original Message-----
> From: Simon Holdsworth [mailto:Simon.[Email Address Removed] Sent: Tuesday, September 17, 2002 10:32 AM
> To: 'sang.[Email Address Removed] LazyDBA.com Discussion
> Subject: RE: Inserting record count into a variable using variable table
> n ame
>
>
> set @record_count = (select count(*) from @tableName)
>
>
> -----Original Message-----
> From: sang.[Email Address Removed] [mailto:sang.[Email Address Removed] Sent: Tuesday, September 17, 2002 8:29 AM
> To: LazyDBA.com Discussion
> Subject: Inserting record count into a variable using variable table
> name
>
> Hi,
>
> I have the following declared:
>
> DECLARE @tableName varchar(20),
> @record_count INT
>
> set @tableName='EMPLOYEES'
>
> I would like to something like this:
>
> select @record_count = count(*) from @tableName
>
> But I get the following error:
> Incorrect syntax near '@table_name'.
>
> I think it is something easy, but I rather seek your assitance instead of
> spending much time figuring out (LazyDBA).
>
> Thank you much.
>
> Sang Nahm
>




-----------------------------------------------------------------
Visit our Internet site at http://www.reuters.com

Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be
the views of Reuters Ltd.

---------------------------------------------------------------------
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page