access result of "dynamic sql query" via transact sql

He,
want i want to do ist creating a dynamic query, execute it and access
the result via transact-sql.
e.g. SELECT * FROM udf_buildquery 'param1' .. WHERE ..
The first thing i tried was to use dynamic sql in udf's, but i realised
very fast, that this wont work.
After that I tried to build the query in a stored procedure but i can't
return the result set to a function or use it in an sql statement (like
SELECT * FROM (exec sp...)). I also tried it with temporary tables but
i also can't access them via userdefined functions. And i can't use
static names for the temp-Tables or even let the user exec the stored
procedure itself, because the user should not see how the whole thing
is working. He should just type "SELECT * FROM [function name]" and not
more.
So if somebody knows how to solve this problem .. please tell me
Thanks,
stephansteph
If I understood you correctly
CREATE TABLE #T
(
col INT
)
INSERT INTO #T EXEC myStoredProcedure
"steph" <stephan@.aiche.info> wrote in message
news:1125924583.250239.32680@.o13g2000cwo.googlegroups.com...
> He,
> want i want to do ist creating a dynamic query, execute it and access
> the result via transact-sql.
> e.g. SELECT * FROM udf_buildquery 'param1' .. WHERE ..
> The first thing i tried was to use dynamic sql in udf's, but i realised
> very fast, that this wont work.
> After that I tried to build the query in a stored procedure but i can't
> return the result set to a function or use it in an sql statement (like
> SELECT * FROM (exec sp...)). I also tried it with temporary tables but
> i also can't access them via userdefined functions. And i can't use
> static names for the temp-Tables or even let the user exec the stored
> procedure itself, because the user should not see how the whole thing
> is working. He should just type "SELECT * FROM [function name]" and not
> more.
> So if somebody knows how to solve this problem .. please tell me
> Thanks,
> stephan
>|||this would work, but i think it won't work in a udf. but i need to do
it with a udf becaus my users just want to type
SELECT * FROM ... and not
CREATE TABLE #T
(
col INT
)
INSERT INTO #T EXEC myStoredProcedure
SELECT * FROM #T
So is there any possibilty to do it with a udf ?|||steph
INSERT INTO #T SELECT <columnsd> FROM dbo.UDF does not work?
"steph" <stephan@.aiche.info> wrote in message
news:1125925509.599913.104850@.g43g2000cwa.googlegroups.com...
> this would work, but i think it won't work in a udf. but i need to do
> it with a udf becaus my users just want to type
> SELECT * FROM ... and not
> CREATE TABLE #T
> (
> col INT
> )
>
> INSERT INTO #T EXEC myStoredProcedure
> SELECT * FROM #T
> So is there any possibilty to do it with a udf ?
>|||INSERT INTO #T SELECT <columnsd> FROM dbo.UDF does not work?
not this way,
it won't work this way
create function dbo.udf ..
returns table
exec sp_creating_temp_table
return (select * from #created_temp_table)
so the user just have to type "SELECT * FROM dbo.udf WHERE .. "|||Please explain your requirement more fully and I'm sure someone can
suggest a better way. It isn't clear to me exactly why you want to do
this. Why can't you just write a query or create a view?
David Portas
SQL Server MVP
--|||I want to do a preselection like "SELECT * FROM ( dbo.udf(@.table_name,
@.other_param) ) WHERE ..." to accelerate the query. So i want to pass
the table name and the preselection params to the udf and the udf
returns the result set. The problem is i got to do some caltculations
for the preselection and then build the preselect query with the
calculated values and i think in this case a view or a selfwritten
query wont work ...
thanks
stephan|||Why not use a parameterized stored procedure? And by the way,
parameterizing table names is a really, really bad idea - and totally
unnecessary in a well-designed system.
David Portas
SQL Server MVP
--|||You didn't explain why you can't use a view or subquery. You can't use
dynamic code in a function.
Have you seen:
http://www.sommarskog.se/share_data.html
http://www.sommarskog.se/dyn-search.html
Without more information all I can suggest is that you should review
your overall design - it sounds like a pretty odd setup to me. Have you
looked at middleware and BI tools?
David Portas
SQL Server MVP
--|||Stephan,
Can you explain what this "preselection" is (preferably with specific
examples - see http://www.aspfaq.com/etiquette.asp?id=5006).
In a well-designed database, it should not be necessary to jump
through hoops in order "to accelerate the query", whatever that
means.
Then again, if when you say "tables are dynamic," you mean
that you never know what tables exist at a given time, I think you
are in bigger trouble than if you were missing some indexes. I have
never seen a design that created and dropped tables willy-nilly that
was not little more than a huge mess.
Asking clear questions about a system like this is like asking
"What color is a chameleon?" Trying to manage one is like
trying to make clothes for amoebae. Nothing fits for more
than a few moments.
Steve Kass
Drew University
steph wrote:

>I already tried to use "parameterized stored procedure" but i can't
>access the result of a sp via t-sql so it won't work for a
>preselection.
>
>
>I know that it is not the best idea, but the tables in the db are
>dynamic, and also i want to use the functionality for more than one
>table and more then one db.
>thanks
>stephan
>
>