problems with a script

Question

Post reply

problems with a script

martin-132733

Grasshopper

Points: 16
More actions
March 12, 2004 at 7:14 am

#161112

hi!
i'm trying to get the following script running but keep getting the error saying that i need to declare the @tablename variable in the select statement. any ideas?

declare @value datetime
set @value = CAST('20040129 15:00:00' AS DATETIME)
DECLARE @tablenames TABLE
(
table_id INT IDENTITY,
table_name SYSNAME
)
INSERT INTO @tablenames (table_name)
SELECT name FROM sysobjects
WHERE type='U'
DECLARE @i INT
DECLARE @tablename SYSNAME
SET @i = 1
WHILE (@i <= (SELECT MAX(table_id) FROM @tablenames))
BEGIN
    SELECT @tablename = table_name
    FROM @tablenames
    WHERE table_id = @i
print @tablename
select * from ( @tablename ) where tstamp < @value
    SET @i = @i + 1
END

/martin

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

wz700 SSCertifiable Points: 7133 More actions · Answer 1

Have to use dynamic sql.

Something like

declare @value datetime

set @value = CAST('20040129 15:00:00' AS DATETIME)

DECLARE @tablenames TABLE

(

table_id INT IDENTITY,

table_name SYSNAME

)

INSERT INTO @tablenames (table_name)

SELECT name FROM sysobjects

WHERE type='U'

DECLARE @i INT

DECLARE @tablename SYSNAME

SET @i = 1

WHILE (@i <= (SELECT MAX(table_id) FROM @tablenames))

BEGIN

SELECT @tablename = table_name

FROM @tablenames

WHERE table_id = @i

print @tablename

exec ('select * from ' + @tablename)

SET @i = @i + 1

END

martin-132733 Grasshopper Points: 16 More actions · Answer 2

ok, great! that works perfect. but what about the "where" clause....?

Jonathan SSC-Insane Points: 20427 More actions · Answer 3

If this is just an ad hoc utility, I'd just cheat and do something like this:

DECLARE @value datetime, @sql varchar(400)

SET @value = '20040129 15:00:00'

SET @sql = 'PRINT ''?'' SELECT * FROM ? WHERE tstamp < ''' + CAST(@value AS varchar) + ''''

EXEC sp_msforeachtable @sql

--Jonathan

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

Although dynamic sql seem to be a solution, one might be inclined to read this for the pitfalls of that approach. Moreover I would also recommend reading the article on dynamic searches on Erlands' homepage.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]