Returning Table (rowset) from Dynamic query

I would like to prepare a query from the arguments of the function and have a return rowset as the result of the prepared query:

To simplify the matter the following prepares a simple query

create function Test(--)

Returns @.ResultSet Table

(

au_lname varchar(40)

)

AS

Begin

declare @.sql varchar(500)

select @.sql ='insert' + @.ResultSet + 'select au_lname from pups..authors'

-- RIGHT HERE I would like the @.sql to get executed Please note that exec(@.sql) does not work

Return

End

Thank you

I think it would be helpful to do a:

select @.sql

and show the results so we can see exactly what you are trying to execute.

Tan is right: You cannot do what you ask inside of a function. Look up the restrictions on UDFs in the CREATE FUNCTION article in books online.

|||you can't use dynamic sql in a function.|||

You have a point. I've tried to use the OPENROWSET but you can not pass the query as prepared variable to it unless there is a catch that I don't know how.

declare @.sql varchar(100)

set @.sql = 'select au_lname, au_fname from pubs..authors'

insert @.T1 select au_lname, au_fname

from openrowset ('SQLOLEDB','server';'uid';'pwd', ?@.sq?) as P

-

How can I construct a stand alone mechanism to return a result (either memory resident -> variable or materialized -> name) as row set type from a prepared query (dynamic) so that it can be utilized as a table within another DML

Mechanism (function/procedure/?)

(assume scope has a prepared query assigned to a varchar type variable)

either

Return a rowset utilizing the prepared query

or

Return a named (View or Table) build by utilizing the prepared query

Thank you

|||Thank you and you are right. However there must be some way to do it beside writing an external code fragment via CLR. HELP if you can|||

You could always dynamically create a string that also contains OPENROWSET, then execute the string and store the results in a temporary table, see below.

Chris

CREATE TABLE #T1

(

au_lname AS VARCHAR(100),

au_fname AS VARCHAR(100)

)

DECLARE @.sql NVARCHAR(4000)

SET @.sql = 'select au_lname, au_fname from pubs..authors'

INSERT INTO #T1(au_lname, au_fname)

EXEC('select au_lname, au_fname from openrowset (''SQLOLEDB'',''server'';''uid'';''pwd'', ' + QUOTENAME(@.sql , '''') + ' ) as P')

SELECT *

FROM #T1

|||

Thank you for your answer. However, you are using EXECUTE. That, KHTan (see above) says it can not work within a Function and he is absolutely right. Therefore, I can not have a stand alone mechanism that can “return” a row set form a dynamically build query. Either the use of OPENROWSET or the problem as stated in my first posting requires executing a DML statement assigned to a variable. Unless there is some other way to get it done then it seems SQL Server is missing an important functionality.

|||Is there any special reason why you must use a function? You can use a stored procedure, pass the parameters, create the dynamic SQL statement, execute it and return any result set you want.

Best regards,
Sami Samir|||Can you please explain what you are exactly trying to do? Please explain the problem you are trying to solve then it will be easier to suggest the correct solution. You are making it harder by trying to use dynamic SQL and the workarounds are just that. There are security risks involved, they are kludgy, hard to maintain, debug and performs terribly.|||

Thank you for asking. This is what I want to get it done:

Please note that the query is prepared from a metadata defined in XML document and that is why it is dynamic. However, there may be a totally different and an elegant way exists to get this done. Any help is appreciated.

1 – Prepare a query (assume assigned to a variable say @.sql)

2 – Obtain a row set (table) type reference pointing the result set of @.sql

3 – Select * from [reference variable pointing to the row set of resulting @.sql]

If SQL Server function would have allowed EXECUTE within or something similar then the following would accomplish the above requirement

create function Test()

Returns @.ResultSet Table

(

au_lname varchar(40)

)

AS

Begin

/* declare and prepare a query */

declare @.sql varchar(500)

select @.sql ='insert' + @.ResultSet + 'select au_lname from pups..authors'

/* populate the return variable */

EXEC(@.sql) /* This is not POSSIBLE */

Return

End

Then I could utilize the function as a table

Select * from Test()