Dynamic SQL Issues with Functions/SP

I am working on a reporting application that uses dynamic SQL to generate
result sets. My client initially used a non-dynamic user-defined function
that returned a table, but when I made the SQL dynamic, it turns out that
EXEC statements cannot be used within a function. Is there a way to return a
table variable from a stored procedure if I convert the function into a
stored procedure? Are there any workarounds for this?
--
Larry Menzin
American Techsystems Corp.>> Is there a way to return a table variable from a stored procedure if I
Table variables cannot be used as parameters or return values, however you
can use regular resultsets for similar functionality. Also see some other
alternatives at:
http://www.sommarskog.se/share_data.html
Anith|||Why are you trying to make udf dynamic? Why not, in your dynamic SQL, simply
include a call to the function?
Thomas
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A7A7359A-B77C-486A-BDF9-2D86141A0B16@.microsoft.com...
>I am working on a reporting application that uses dynamic SQL to generate
> result sets. My client initially used a non-dynamic user-defined function
> that returned a table, but when I made the SQL dynamic, it turns out that
> EXEC statements cannot be used within a function. Is there a way to return
a
> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
> --
> Larry Menzin
> American Techsystems Corp.|||You can return a result set from a Stored Procedure with a SELECT statement.
Here's a really simplified example that uses dynamic SQL:
CREATE PROCEDURE dbo.usp_test
AS
DECLARE @.MySQL AS NVARCHAR(4000)
SET @.MySQL = N'SELECT * FROM master.dbo.sysmessages'
EXEC dbo.sp_executesql @.MySQL
GO
EXEC dbo.usp_test
GO
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:A7A7359A-B77C-486A-BDF9-2D86141A0B16@.microsoft.com...
>I am working on a reporting application that uses dynamic SQL to generate
> result sets. My client initially used a non-dynamic user-defined function
> that returned a table, but when I made the SQL dynamic, it turns out that
> EXEC statements cannot be used within a function. Is there a way to return
> a
> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
> --
> Larry Menzin
> American Techsystems Corp.|||> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
No
Can you explain what are you trying to accomplish?
AMB
"Larry Menzin" wrote:

> I am working on a reporting application that uses dynamic SQL to generate
> result sets. My client initially used a non-dynamic user-defined function
> that returned a table, but when I made the SQL dynamic, it turns out that
> EXEC statements cannot be used within a function. Is there a way to return
a
> table variable from a stored procedure if I convert the function into a
> stored procedure? Are there any workarounds for this?
> --
> Larry Menzin
> American Techsystems Corp.|||I am trying to join 4 result sets together, where each set is currently
generated as a table output variable from a UDF. UDF output is used directly
in JOIN statements in a stored procedure. If I convert UDFs to stored
procedures can I use stored procedure result sets in joins like UDF table
variables are used in joins, i.e.,
Select * From ExecSP1(parameters1) JOIN ExecSP1(Parameters2) JOIN
ExceSP1(Parameters3) ON ...
Larry Menzin
American Techsystems Corp.
"Alejandro Mesa" wrote:
> No
> Can you explain what are you trying to accomplish?
>
> AMB
> "Larry Menzin" wrote:
>|||You can use VIEWs in the manner you're talking about. Another option is to
create Temporary Tables with SELECT INTO or INSERT INTO in your SP and JOIN
them.
"Larry Menzin" <LarryMenzin@.discussions.microsoft.com> wrote in message
news:0E155215-A119-4980-B1B9-7DFC110E22AF@.microsoft.com...
>I am trying to join 4 result sets together, where each set is currently
> generated as a table output variable from a UDF. UDF output is used
> directly
> in JOIN statements in a stored procedure. If I convert UDFs to stored
> procedures can I use stored procedure result sets in joins like UDF table
> variables are used in joins, i.e.,
> Select * From ExecSP1(parameters1) JOIN ExecSP1(Parameters2) JOIN
> ExceSP1(Parameters3) ON ...
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Alejandro Mesa" wrote:
>|||Larry,
No, you can not reference stored procedure from the "from" clause, but as
Anith pointed, there are other alternatives, like capturing the sp resultset
into a table (temporary or permanent) and use that table in subsequent
statements. Click the link posted by Anith Sen.
AMB
"Larry Menzin" wrote:
> I am trying to join 4 result sets together, where each set is currently
> generated as a table output variable from a UDF. UDF output is used direct
ly
> in JOIN statements in a stored procedure. If I convert UDFs to stored
> procedures can I use stored procedure result sets in joins like UDF table
> variables are used in joins, i.e.,
> Select * From ExecSP1(parameters1) JOIN ExecSP1(Parameters2) JOIN
> ExceSP1(Parameters3) ON ...
> --
> Larry Menzin
> American Techsystems Corp.
>
> "Alejandro Mesa" wrote:
>|||> If I convert UDFs to stored
> procedures can I use stored procedure result sets in joins like UDF table
> variables are used in joins, i.e.,
No and I don't see a reason why you would need to do that. You should be abl
e to
do the following:
Select *
From dbo.UDF1(parameters) As UDF1
Join dbo.UDF2(parameters) As UDF2
On UDF1.Key = UDF2.Key
...
This assumes that all of the functions are table-valued functions. If there
is
commonality amongst the parameters in the functions, you could create a sing
le
stored procedure that returns the data like so:
Create Procedure ReportXYZ(parameter1 as ...)
As
Select *
From dbo.UDF1(parameters) As UDF1
Join dbo.UDF2(parameters) As UDF2
On UDF1.Key = UDF2.Key
Return
Notice that no dynamic SQL is needed for any of this. You would simply call
the
stored procedure which would return the raw data for you.
Thomas|||Here's one of I'm sure many articles on this subject:
http://www.databasejournal.com/feat...cle.php/3386661
jp
Larry Menzin wrote:
> Alejandro,
> Can I capture the result set from the SP into a table variable, i.e.,
> DECLARE @.CurrentPeriodDataPrelim TABLE(
> Product VarChar(40)
> ,Region varchar(40)
> ,Market varchar(40)
> ,Channel varchar(40)
> ,MRC float
> ,Net float
> ,NumerLabel nVarChar(150)
> )
>
> Declare @.strSQL nvarchar(2000)
> Set @.strSQL = N'
> INSERT INTO @.CurrentPeriodDataPrelim (Product, Region, Market, Channel, MR
C,
> NET, NumerLabel)
> SELECT cat as Product, ...
> Or do I have to use temp tables with the '#' designation if I don't want t
o
> use permanent tables. Also, don't I worry about temp tables going out of
> scope?
>
>