Simon,

Thank you. Learned a lot from you guys, as always.

Sang

-----Original Message-----
From: Simon Holdsworth [mailto:Simon.[Email Address Removed] Tuesday, September 17, 2002 5:23 PM
To: 'sang.[Email Address Removed] [Email Address Removed] RE: Inserting record count into a variable using variable table
n ame


If you want to return the value to a variable for further manipulation or
reporting then you will be better off using the method that Tom had written.
It would look something like this...

DECLARE @TABLENAME VARCHAR(20)
DECLARE @RECORDCOUNT INT
set @TABLENAME='sysobjects'

exec ( 'select COUNT(*) AS RecordCount into ##Tmp_Table1 from ' +
@TABLENAME)
select @RECORDCOUNT = RecordCount from ##Tmp_Table1

--Do something with the record count
--before dropping the global temp table
print @RECORDCOUNT

Drop table ##Tmp_Table1


-----Original Message-----
From: sang.[Email Address Removed] [mailto:sang.[Email Address Removed]
Sent: Tuesday, September 17, 2002 2:45 PM
To: Simon.[Email Address Removed] sang.[Email Address Removed] RE: Inserting record count into a variable using variable
table n ame

Simon,

Thank you so much. Could you do me a another favor and let me know how I
can assigned the output of EXEC(@CMD) to a variable, @record_count defined
as INT?

Thanks.

Sang
-----Original Message-----
From: Simon Holdsworth [mailto:Simon.[Email Address Removed] Tuesday, September 17, 2002 2:28 PM
To: 'sang.[Email Address Removed] RE: Inserting record count into a variable using variable table
n ame


Here, this works.
Simon

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

-----Original Message-----
From: sang.[Email Address Removed] [mailto:sang.[Email Address Removed]
Sent: Tuesday, September 17, 2002 11:25 AM
To: Simon.[Email Address Removed] RE: Inserting record count into a variable using variable
table n ame

Simon,

Thanks anyway.

Sang

-----Original Message-----
From: Simon Holdsworth [mailto:Simon.[Email Address Removed] Tuesday, September 17, 2002 11:14 AM
To: 'sang.[Email Address Removed] RE: Inserting record count into a variable using variable table
n ame


That's a good point. It actually only seems to work when the variable is in
the where clause.

-----Original Message-----
From: sang.[Email Address Removed] [mailto:sang.[Email Address Removed]
Sent: Tuesday, September 17, 2002 8:44 AM
To: Simon.[Email Address Removed] [Email Address Removed] RE: Inserting record count into a variable using variable
table n ame

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] 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

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