Declare Tablename error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Declare Tablename error

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2007-05-17 : 12:10:45

Do anyone know a way to do this? I get an error saying I have to declare @tablename but I already have declared it.

select @columnname

FROM @tablename

WHERE @columnname like '.%.%'

OR @columnname like '%.%.%'

The full text of the script is…

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_column_names]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

drop table [dbo].[tmp_column_names]

GO

select table_name, column_name
into tmp_column_names
from information_schema.columns
where column_name like '%pct%'
or column_name like '%pctg%'

GO

DECLARE @tablename varchar(100)
DECLARE @columnname varchar(100)
DECLARE @qry varchar(2000)
DECLARE cur CURSOR FOR SELECT table_name, column_name FROM tmp_column_names

OPEN cur

FETCH NEXT FROM cur
INTO @tablename, @columnname

WHILE @@FETCH_STATUS = 0
BEGIN

select @columnname
FROM @tablename
WHERE @columnname like '.%.%'
OR @columnname like '%.%.%'

END

close cur

deallocate cur

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-17 : 12:35:15

"I get an error saying I have to declare @tablename but I already have declared it."

You will need to use dynamic sql here. But let me warn you this is messy thing to do.

EXEC('select ' + @columnname + '
FROM ' + @tablename + '
WHERE ' + @columnname + ' like ''.%.%''
OR ' + @columnname + ' like ''%.%.%''')

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

nr
SQLTeam MVY

12543 Posts

Posted - 2007-05-17 : 12:35:55

You can't use a variable for a tablename. You need to use dynamic sql.
It's expecting it to be a table variable rather than a string hence the error.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources