Dynamic SQL in function - 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 2012 Forums

Transact-SQL (2012)

Dynamic SQL in function

Author

Topic

noamg
Posting Yak Master

215 Posts

Posted - 2013-06-03 : 10:45:58

is there any way to call sp_executeSQL in function ?

Noam Graizer

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-06-03 : 11:05:36

No, but maybe you can tell us your problem?

Too old to Rock'n'Roll too young to die.

noamg
Posting Yak Master

215 Posts

Posted - 2013-06-03 : 11:41:27

The SP gets a set of dynamic conditions.
every condition has to query a different table/s and output list of UserIDs.
now, I need to inner join among the outputs.
I think to create a function for each condition ...
then inner join between the functions ?

Noam Graizer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-03 : 23:58:47

why function? why not use temporary tables with SP itself and do the join?
None of explained conditions require use of function!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

noamg
Posting Yak Master

215 Posts

Posted - 2013-06-04 : 02:27:28

can we do this:

select ...
from sp_getusers( param1 ) as p1
inner join sp_getbla( param2 ) as p2
on p1.col1 = p2.col2

this is the idea to what i am try to get

Noam Graizer

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 02:42:30

quote:
Originally posted by noamg

can we do this:

select ...
from sp_getusers( param1 ) as p1
inner join sp_getbla( param2 ) as p2
on p1.col1 = p2.col2

this is the idea to what i am try to get

Noam Graizer

Nope
but you can use this


CREATE TABLE #Temp1
(
--structure same as resultset of sp_getusers
)

CREATE TABLE #Temp2
(
--structure same as resultset of sp_getbla

)

select ...
  from #temp1 as p1
    inner join #temp2 as p2
    on p1.col1 = p2.col2

or even the second method discussed here (though not recommmended as it relies upon distributed query methods)

http://beyondrelational.com/modules/2/blogs/70/posts/10812/select-columns-from-exec-procedurename-is-this-possible.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources