Exec a StoredProc from in a Select statement that returns mult

Tony,
The called sp is too big to put in the SELECT. It does a lot, creates and
used temp tables. . . I don't think I would want to go that route.
Because it uses temp tables, I couldn't make it a udf. I have not used a
scalar function. How does it differ from a udf? Can I create and use temp
tables in it?
Thanks,
Steve
"Tony Sellars" wrote:
> Sounds like you have a couple of options. One would be to incorporate the
> logic from the procedure you are calling into the select statement you
> mentioned. Another would be to create a scalar function which returns the
> output1 value you are looking at and use it in place of the subselect in y
our
> sample. The function option would most likely not perform as fast as
> incorporating the logic directly.
> HTH
> --Tony
> "SteveInBeloit" wrote:
>Scalar functions are simply a type of udf (take a look at bol under 'create
function' for all the details). Given what you are saying here are some
other options. The functions can use table variables so it may be possible
to write your procedure as a function depending on what your code does (ther
e
are some coding limitations within functions). Another option would be to
rewrite your spMyProc to take a set of values (say in an xml parameter) and
return a result set which either matches what you would ultimately select ou
t
(and not write the calling procedure) or that could be used in an INSERT
EXEC statement to trap the results in a temp table and be used in the final
join.
Hope this does more than just muddy the water for you.
--Tony
"SteveInBeloit" wrote:
> Tony,
> The called sp is too big to put in the SELECT. It does a lot, creates and
> used temp tables. . . I don't think I would want to go that route.
> Because it uses temp tables, I couldn't make it a udf. I have not used a
> scalar function. How does it differ from a udf? Can I create and use tem
p
> tables in it?
> Thanks,
> Steve
> "Tony Sellars" wrote:
>