executing Dynamic subquery - 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

Transact-SQL (2000)

executing Dynamic subquery

Author

Topic

mna_4u@hotmail.com
Starting Member

4 Posts

Posted - 2008-06-11 : 06:27:12

DECLARE @SQL NVARCHAR(100)
SET @SQL = 'SELECT TOP 1 PubID FROM Pubs'
SELECT * FROM Pubs WHERE pubid IN(exec (@SQL))

My problem is that "exec (@SQL)" is not working properly.
I have also used sp_executesql but the result was not as I desired.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 06:30:43

TOP without an ORDER BY get an arbitrary record.
If you also add an ORDER BY you make sure you get same result every time.

SELECT		TOP 1
		*
FROM		Pubs
ORDER BY	PubID

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

mna_4u@hotmail.com
Starting Member

4 Posts

Posted - 2008-06-11 : 06:37:06

Dear Peso,
Thanks for quick response.

My problem is not using top
SET @SQL = 'SELECT TOP 1 PubID FROM Pubs' is dynamic statement
Which might be any thing and return list of pubID

and in the second step I used (exec (@SQL)) to get the results

Basically I want to use exec() function as subquery

Thanks again
If any thing that is not clear let me please ask to me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 06:57:13

quote:
Originally posted by mna_4u@hotmail.com

DECLARE @SQL NVARCHAR(100)
SET @SQL = 'SELECT TOP 1 PubID FROM Pubs'
SELECT * FROM Pubs WHERE pubid IN(exec (@SQL))

My problem is that "exec (@SQL)" is not working properly.
I have also used sp_executesql but the result was not as I desired.

Thanks

First do this

DECLARE @SQL NVARCHAR(100)
SET @SQL = 'SELECT TOP 1 PubID FROM Pubs'
put result onto a temporary table
Insert #Temp
exec (@SQL)

then use it in query
SELECT * FROM Pubs WHERE pubid IN(select field FROM #Temp)

mna_4u@hotmail.com
Starting Member

4 Posts

Posted - 2008-06-11 : 07:04:31

Dear Visakh16,

Thanks for your quick response. Your are right. I am also doing this approch. But the problem is that "exec (@SQL)" works well when we are saving data into temp table. But it is not working at IN() Function.

Basically I want to remove intermiate step for using temp table. Because these are my core procedures and they I don't want to use temp tables.

More help is requried.

Thanks in advance......

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-11 : 07:14:11

quote:
Originally posted by mna_4u@hotmail.com

Dear Visakh16,

Thanks for your quick response. Your are right. I am also doing this approch. But the problem is that "exec (@SQL)" works well when we are saving data into temp table. But it is not working at IN() Function.

Basically I want to remove intermiate step for using temp table. Because these are my core procedures and they I don't want to use temp tables.

More help is requried.

Thanks in advance......

You cant use Exec directly inside the IN subquery.
I assume the scenario you posted was just for illustration becuase you dont really need dynamic sql for scenario you posted.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 07:14:56

Please please read about IN statement in Books Online (Microsoft SQL Server help file).
There is all the syntax you ever need to know.

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

mna_4u@hotmail.com
Starting Member

4 Posts

Posted - 2008-06-11 : 07:27:00

Dear Peso,Visakh16

Thanks for your kind help.

Visakh16 you are saying right. I am just describing my scenrio.
Peso I have already checked all the online MS SQL Server books.
But could not find anything regarding my scenrio.

Currently my SP's are working using #temp tables

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-11 : 07:35:49

IN takes

1) A comma-separated list of constant values.
2) A SELECT-statement returning a single-column resultset.

Your EXEC statement fails both rules.
Anyway according to Books Online, but then again, I might be wrong.

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