what is wrong in query??

Question

Post reply

what is wrong in query??

Rashid Yusuf Shaikh

SSC Journeyman

Points: 77
More actions
May 19, 2005 at 2:15 am

#66429

I am trying to use tablename in variable and using in select statement with both decalration as follows-
use northwind
DECLARE @tablename sysname
--DECLARE @tablename varchar(10)
set @tablename = 'Employees'
SELECT EmployeeID, * FROM @tablename
It flashes following error message.
Must declare the variable '@tablename'.
Why??? . Please anyone explain

Viewing 8 posts - 1 through 7 (of 7 total)

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

You cannot use a tablename with a variable like that.

Could you please explain what you are trying to do?

(..and I'm not going to recommend using dynamic SQL without any reason for it - you shouldn't go that way either as a first hand choice.)

/Kenneth

Rashid Yusuf Shaikh SSC Journeyman Points: 77 More actions · Answer 2

I am searching for tables where a given field used through following query

use Northwind

DECLARE @FieldName VARCHAR(15)

SET @FieldName = 'EmployeeID'

SELECT O.name AS

FROM dbo.sysobjects O

INNER JOIN dbo.syscolumns C ON O.id = C.id WHERE (C.name = @FieldName)

After getting list of tables where the field is used, I want to look for a particular value of the field in these tables.

I am using the following query with each of the table name in list as variable:

DECLARE @tablename sysname

--DECLARE @tablename varchar(10)

set @tablename = 'Employees'

SELECT EmployeeID, * FROM @tablename

Here i am failing and looking for guidance.

Paul Cresham Hall of Fame Points: 3623 More actions · Answer 3

DECLARE @tablename sysname

--DECLARE @tablename varchar(10)

set @tablename = 'Employees'

EXEC('SELECT EmployeeID, * FROM ' + @tablename)

Rashid Yusuf Shaikh SSC Journeyman Points: 77 More actions · Answer 4

Thanks, Paul. That works fine. Taking your clue further I wrote as:

USE NORTHWIND

DECLARE @tablename SYSNAME

SET @tablename = 'EMPLOYEES'

1. SELECT @tablename , E.EMPLOYEEID FROM EMPLOYEES E

2. EXEC ('SELECT E.EMPLOYEEID FROM ' + @tablename + ' E ')

3. EXEC ('SELECT ' + @tablename + ' , E.EMPLOYEEID FROM ' + @tablename + ' E ')

In above 3 statements first 2 statements works fine. but third statement fails. Please help.

Rob Farley SSCrazy Points: 2739 More actions · Answer 5

3. EXEC ('SELECT ' + @tablename + ' , E.EMPLOYEEID FROM ' + @tablename + ' E ')

This will fail because it translates to:

SELECT tblEmployees , E.EMPLOYEEID FROM tblEmployees E

So it's going to look for a column called tblEmployees, which I doubt exists.

If you just want to be able to show what table you looked in, then try:

EXEC ('SELECT ''' + @tablename + ''' tablename , E.EMPLOYEEID FROM ' + @tablename + ' E ')

But I'm not entirely sure why you'd want to do this.

RobF

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Rob Farley SSCrazy Points: 2739 More actions · Answer 6

Of course... when I refer to 'tblEmployees' above, you would have EMPLOYEES. I just want to make it clear that it's a table name.

Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au

Rashid Yusuf Shaikh SSC Journeyman Points: 77 More actions · Answer 7

Thanks Rob and to all who put their brains to work for my sake.

As indicated by Rob, I wanted to show the table name in the results.

Purpose of the exercise was to search the tables with required field having particular value. I did this in 2 steps.

1. searched the tables with required field

2. searched the field with the particular value.

Hope I have made the matter clear enough.