How to do this? - 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 2000 Forums

SQL Server Development (2000)

How to do this?

Author

Topic

MasErf
Starting Member

5 Posts

Posted - 2008-06-26 : 03:35:13

Hi there
Plz. help me to write this SQL command:

Select * From xxx

and xxx comes from:

SELECT TblName
FROM My_AlltblNames
WHERE myfld1='tblOne'

the 2nd. query returns a table name that I wnat to use in 1st. query as table name in From Clause.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-06-26 : 03:47:21

You'll have to use dynamic sql for this. Something like this might get you on the way:

DECLARE 
  @sql nvarchar(1000)

SELECT @sql = N'SELECT * FROM ' + TblName 
FROM My_AlltblNames 
WHERE myfld1='tblOne'

EXEC sp_executesql @sql

- Lumbago

MasErf
Starting Member

5 Posts

Posted - 2008-06-26 : 04:17:35

tanx but it shows this error message:

Invalid object name 'tblExchangeBroker'

**Note that 'tblExchangeBroker' comes from TblName & 'tblExchangeBroker' exists in my database.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-06-26 : 04:27:52

Script works great when I try it here. What does your actual script look like and are you running it on the same database? Make sure to use 3-part table naming if My_AlltblNames and tblExchangeBroker are in different databases...

- Lumbago

MasErf
Starting Member

5 Posts

Posted - 2008-06-26 : 04:32:26

here is my actual script:

DECLARE @sql nvarchar(1000)
SELECT @sql = 'SELECT * FROM ' + TblName
FROM [MyInfoSystem].[dbo].My_AlltblNames
where fldDesc like '%Exchange%'
EXEC sp_executesql @sql

No both of them are on the same database

MasErf
Starting Member

5 Posts

Posted - 2008-06-26 : 04:35:05

here is my actual script:

DECLARE @sql nvarchar(1000)
SELECT @sql = 'SELECT * FROM ' + TblName
FROM [MyInfoSystem].[dbo].My_AlltblNames
where fldDesc like '%Exchange%'
EXEC sp_executesql @sql

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-06-26 : 04:40:57

Does this work:

DECLARE @sql nvarchar(1000)
SELECT @sql = 'SELECT * FROM [MyInfoSystem].[dbo].' + TblName
FROM [MyInfoSystem].[dbo].My_AlltblNames
where fldDesc like '%Exchange%'
EXEC sp_executesql @sql

- Lumbago

MasErf
Starting Member

5 Posts

Posted - 2008-06-26 : 04:41:29

Oh tanx the correct script is:
DECLARE @sql nvarchar(1000)
SELECT @sql = 'SELECT * FROM [MyInfoSystem].[dbo].' + TblName
FROM [MyInfoSystem].[dbo].My_AlltblNames
where fldDesc like '%Exchange%'
EXEC sp_executesql @sql

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources