nested select statement - 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)

nested select statement

Author

Topic

chakri
Starting Member

2 Posts

Posted - 2008-02-19 : 01:09:00

Select (select parm_content FROM Univgrid_set_parms where parm_Id=1 and UnivGrid_ID=1000) -- returns column names
from (select parm_content FROM Univgrid_set_parms where parm_Id=2 and UnivGrid_ID=1000) -- returns Table name
where (select parm_content FROM Univgrid_set_parms where parm_Id=3 and UnivGrid_ID=1000)

while executing am getting error.. can anyone correct me how to change the above query?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-19 : 02:02:12

Cant understand what you want to achieve here. Can you provide your table structure and what your desired o/p is?

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-02-19 : 02:24:26

Hi,
You need to use dynamic SQL, try this

DECLARE @cols VARCHAR(MAX),
@tbl VARCHAR(MAX),
@cond VARCHAR(MAX),
@strSQl VARCHAR(MAX)

SELECT @cols = '',
@tbl = '',
@cond = '',
@strSQl = ''

SELECT @cols = (SELECT parm_content FROM Univgrid_set_parms WHERE parm_Id=1 AND UnivGrid_ID=1000),
@tbl = (SELECT parm_content FROM Univgrid_set_parms WHERE parm_Id=2 AND UnivGrid_ID=1000),
@cond = (SELECT parm_content FROM Univgrid_set_parms WHERE parm_Id=3 AND UnivGrid_ID=1000)

SELECT @strSQl = 'SELECT ' + @cols + ' FROM ' + @tbl + ' WHERE ' + @cond

PRINT @strSQl
EXEC (@strSQl)

chakri
Starting Member

2 Posts

Posted - 2008-02-19 : 03:10:51

Thanks peter its working perfectly.

quote:
Originally posted by PeterNeo

Hi,
You need to use dynamic SQL, try this

DECLARE @cols VARCHAR(MAX),
@tbl VARCHAR(MAX),
@cond VARCHAR(MAX),
@strSQl VARCHAR(MAX)

SELECT @cols = '',
@tbl = '',
@cond = '',
@strSQl = ''

SELECT @cols = (SELECT parm_content FROM Univgrid_set_parms WHERE parm_Id=1 AND UnivGrid_ID=1000),
@tbl = (SELECT parm_content FROM Univgrid_set_parms WHERE parm_Id=2 AND UnivGrid_ID=1000),
@cond = (SELECT parm_content FROM Univgrid_set_parms WHERE parm_Id=3 AND UnivGrid_ID=1000)

SELECT @strSQl = 'SELECT ' + @cols + ' FROM ' + @tbl + ' WHERE ' + @cond

PRINT @strSQl
EXEC (@strSQl)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-19 : 03:20:12

In SQL Server 2005, this is much simpler
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources