March 3, 2010 at 10:24 am
Good day all,
trying to use variables to hold two pieces of information.
The first being the SQL server name, and the second which is a table name, pulled from another table via a loop.
these two variables are then strung together and held in a third variable which is then used in a select query.
Tested the variables and it prints the correct string and loops perfectly, however the SELECt part does not like calling
the table from a variable. Hard-coded it all works well but using the variable it falls over.
I'm hoping my syntax is wrong so it can be corrected but if not then my method of using a variable for a table
name in the SELECT statement is flawed and requires a re-think.
The code is as follows, can anyone see why the SELECt statement does not like the variable?
DECLARE @dbName varchar(50)
DECLARE @tblName varchar(50)
DECLARE @DBTbl varchar(50)
set @dbName = 'DBName.DBO.'
set @tblName = ''
set @DBTbl = ''
While @tblName is not null
begin
select @tblName = min(tblThatholdsTableNames)
from tblThatholdsTableNames
WHERE fldFromThatTable> @tblName
EDIT: I forgot to include this line while messign with the original code
set @DBTbl = @dbName + @tblName
EDIT: Which combiines both variables into one, so the original SET for that variable was remmed out
print @DBTbl
This part works fine, if I add END, GO, it prints the list of tablenames in full.
This point here it falls over...
if @tblName is not null
begin
SELECT COUNT(AField) AS RecCount
FROM @DBTbl
end
go
The error message I get is:-
Must declare the variable '@DBTbl'.
so is it not possible to pull a table from a variable? I'm using SQL 2000.
Thanks in advance,
Mitch...
March 3, 2010 at 11:27 am
what you are doing at the end is dynamic sql - http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1
you will want to change:
SELECT COUNT(AField) AS RecCount
FROM @DBTbl
To something like this:
EXEC( 'SELECT COUNT(AField) AS RecCount FROM ' + @DBTbl )
-- Cory
March 3, 2010 at 12:01 pm
Cory that is great.
Just ran it and it works perfectly, thank you so much!!
March 21, 2010 at 10:39 am
If you don't need 100% accurate results (This should be at least 99.5% accurate), you can just do something like this:
SELECT OBJECT_NAME(id) AS [TableName], rowcnt AS [RowCount]
FROM sysindexes I
WHERE indid IN (1,0)
AND OBJECTPROPERTY(id, 'IsUserTable') = 1
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply