Dynamic Table Name in SELECT statement

[Shilohcity]

Hi there

I am trying to do the following:

SET @cnt = (SELECT COUNT (*) FROM @dynamicTableName)

This keeps on erroring on the use of a local variable for the table name. If I replace the variable with the hard coded table name it works fine.

I know how to do this as:

SET @SQLString = "SELECT COUNT (*) FROM " + @dynamicTableName
EXEC(@SQLString)

but I need the return value as a variable so I can work with it.

Any suggestions or ideas greatfully received.

Cheers
Justin. "Creativity is the ability to introduce order into the randomness of nature." Eric Hoffer

Visit me at

http://www.dfodesign.co.nz

 

[wsmall73]

ONE of many ways to skin that cat...
CREATE TABLE #table
(
mycount int
)

DECLARE @sqlstring varchar(5000)
DECLARE @cnt int

SELECT @sqlstring=' INSERT #table SELECT count(*) FROM '+ @table
EXEC (@sqlstring)
SELECT @cnt=mycount FROM #table

you could probably also set the count as a seperate stored procedure.... and do something like...

SELECT @cnt=COUNT(*) FROM sp_getCount @table... check this syntax ...

 

[Shilohcity]

Thanks wsmall

Both good suggestions. I was trying to avoid having to create any temp tables or SP's as I only needed a simple count of records in a table. I actually found what I think is a pretty good solution here :

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_20092218.html

This opens a cursor containing the query result but using the EXEC command so local variables are allowed in the querystring.

Thanks for your suggestions.

Justin. "Creativity is the ability to introduce order into the randomness of nature." Eric Hoffer

Visit me at

http://www.dfodesign.co.nz

 

[tlbroadbent]

See faq183-3132 - Executing Dynamically Created T-SQL Strings.
If you want to get the best answer for your question read faq183-874 and thread183-468158.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[Shilohcity]

Hi tlbroadbent

Thanks for the additional suggestions but although the FAQ deals with the use of dynamic variables in queries using the EXEC() command it doesn't explain how to return the variable to another variable like:

SET @cnt = SELECT COUNT (*) FROM @dynamicTableName
print @cnt -- Prints number of records


If this was done as

SET @cnt = "SELECT COUNT (*) FROM " + @dynamicTableName
EXEC(@cnt)
print @cnt -- Prints SQL Statement

This had me puzzled for ages but using a cursor as outlined in the link from my previous posting I was able to get what I was after.

Thanks again for the suggestions.

Cheers
Justin.
"Creativity is the ability to introduce order into the randomness of nature." Eric Hoffer

Visit me at

http://www.dfodesign.co.nz

 

[sunila7]

Try this.....

declare @dynamicTableName varchar(10)
Declare @SQL nvarchar(1000)
declare @cnt int



SELECT @SQL = 'SELECT COUNT (*) FROM ' + @dynamicTableName

EXECUTE sp_executesql @SQL, N'@cntINT OUTPUT', @cnt OUTPUT
print @cnt


Hope it helps

Sunil

 

[Shilohcity]

Thanks Sunil.

That works much more nicely than the way I found using a cursor. It should be a little bit easier on memory as well.

One thing if anybody else is looking at the solution above there should be a space in the 2nd line like:

EXECUTE sp_executesql @SQL, N'@cnt INT OUTPUT', @cnt OUTPUT

Thanks again.


Justin. "Creativity is the ability to introduce order into the randomness of nature." Eric Hoffer

Visit me at

http://www.dfodesign.co.nz