Using EXEC in select Query

[lpatnaik]

Hi,

I need a list of IDs in the where clause of my sql query which is encapsulated in my stored proc. Ex:

....
select * from table1 where col1 in ('a','b','c'....)
...
These values ('a','b','c'....) can be fetched from a dynamic select query whose where clause I am sending as a paremeter to the proc. Ex:
exec('select col1 from table1' + @whereCondition)

The problem i am facing is that I cannot use exec within a select query. I also tried with a user defined function in order to return a table with the values. In that also, since I have to use the exec statement, the insert exec statement isnt working.

I am stuck with this @wherecondition as the client does not want to change the interface which sends it to the proc. He wants all changes to be done only on the procs.
Please advice.

regards
LP

 

[vbkris]

try this:

create table #TempTab(Col1 char(1))

exec('insert into #TempTab select col1 from table1' + @whereCondition)

select * from table1 where col1 in (select Col1 from #TempTab)


Known is handfull, Unknown is worldfull

 

[Geoka]

You cannot directly use stored procedure output like that. Use UDF instead.

 

[vbkris]

>>exec('select col1 from table1' + @whereCondition)

is not an SP. its a part of an SP. thats what i assumed...

Known is handfull, Unknown is worldfull

 

[Geoka]

Hi VbKris,

My Comment was not against ur reply. You said it correct. I was telling in common to Ipatnaik about using SP directly inside a Select Statement

 

[vbkris]

oh, k.

Known is handfull, Unknown is worldfull