Question about select count(*) from "@table"

[bloombergCR]

declare @table varchar(20)
set @table = 'Persons'
select count(*) from "@table"

Hi,

Would you please tell me why the above script gave me an error meassage of Must declare the variable '@table'?
How can I improve it?

Thanks alot.

Bloomberg

 

[ptheriault]

because @table is not a table variable.

Is this what you are trying to do?

declare @table table (id int)

insert into @table values(1)
insert into @table values(2)
insert into @table values(3)

select count(*) from @table

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

Or, maybe it's dynamic SQL you are looking for.

declare @table varchar(20)
set @table = 'Persons'

Declare @SQL VarChar(8000)
Set @SQL = 'select count(*) from [' + @table + ']'

exec (@SQL)

If you decide to use this code, do a google search on Dynamic SQL first.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bloombergCR]

Thanks for helping me. It works.

One more thing,

declare @table varchar(20),
@stmt varchar(200),
@count int

set @table = 'Persons'
set @stmt = 'select @count = count(*) from [' + @table + ']'

execute (@stmt)

I want the @count holding the number of records.

Thanks again!

Bloomberg

 

[bloombergCR]

declare @table varchar(20),
@stmt varchar(200),
@count int

set @table = 'Persons'
set @stmt = N'select ' + QUOTENAME(@count) + ' = count(*) from [' + @table + ']'
execute (@stmt)

Can you tell why this script outputs no error, no result?

Bloomberg

 

[sunila7]

To return data that you want to use later when using dynamic SQL use sp_executesql

Here are a few links.

http://support.microsoft.com/kb/262499

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=41413

Sunil

 

[bloombergCR]

Dynamic SQL use sp_executesql worked me out.

Thank you very much!


Bloomberg

 

[bloombergCR]

I still have problems.

DECLARE @SQLString nvarchar(2000),
@paramList nvarchar(1000),
@RtnCount int

SET @SQLString = N'SELECT @RtnCount = COUNT(t1.PersonsID) FROM @thisTable t1
INNER JOIN Students
ON Students.PersonID = t1.PersonID
WHERE Students.StudentsID = CAST(@thisID as varchar(10))'

SELECT @ParamList = N'@thisTable varchar(1000),
@thisID int,
@RtnCount int OUTPUT'

EXEC SP_EXECUTESQL @SQLString,
@ParamList,
@thisTable = 'Persons',
@thisID = 124356,
@RtnCount = @RtnCount OUTPUT


I got an error of Must declare the variable '@thisTable'. I am confused.

Because the table will be changed, TABLENAME should be dynamic. I wonder if TABLENAME can be a parameter?

Thank you!

Bloomberg

 

[sunila7]

How about this?

DECLARE @SQLString  nvarchar(2000),
        @paramList  nvarchar(1000),
        @RtnCount   int 

DECLARE @thisTable varchar(50)
SET @thisTable = 'Persons'

SET @SQLString = N'SELECT @RtnCount = COUNT(t1.PersonsID) FROM ' + @thisTable            
+ 't1 INNER JOIN Students  
ON Students.PersonID = t1.PersonID  
WHERE Students.StudentsID = CAST(@thisID as varchar(10))'          
         
SELECT @ParamList = N'@thisID int,   
                      @RtnCount int OUTPUT'

EXEC SP_EXECUTESQL @SQLString, 
                   @ParamList, 
                   @thisID    = 124356,
                   @RtnCount  = @RtnCount OUTPUT

SELECT @RtnCount

Sunil

 

[bloombergCR]

Sunil,

It works well. Thx alot.

Bloomberg