Ahh, I didn't reflect over OPENQUERY not accepting a variable for query. Well, you learn
something every day :-).
> Hello, Tibor!
> Yeah...I had considered OPENQUERY/OPENROWSET, but given that my dynamic
> query is in a string, and that those functions don't take variables as
> arguments...I wasn't able to get too far with it. ;-)
> I appreciate what BP reiterated about not allowing UDFs to change the global
> database state (and that it would be too difficult to programmatically
> determine what your query was doing to ascertain whether it was just a
> simple SELECT).
> But I wish it did... ;-)
> Thanks guys!
> John Peterson
> > I think that a workaround might be to execute the stuff using
> OPENROWSET/OPENQUERY. Not very
> > nice, and remember that MS tried to explicitly avoid dynamic SQL inside
> UDF's. Something to
> > consider before using such a workaround.
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...
> > > John, Bruce,
> > > Sorry, but one of the fundamentals of UDF's is, as stated in the section
> > > "CREATE FUNCTION" right at the top: "User-defined functions cannot be
> used
> > > to perform a set of actions that modify the global database state." This
> > > means that one cannot do INSERTs, UPDATEs, and DELETEs to permanent
> tables,
> > > for example, from within a UDF. Dynamic SQL is a very powerful tool, and
> one
> > > that can "modify the global database state". While it might be nice if
> the
> > > code within the dynamic SQL were examined at each call of the UDF to see
> if
> > > it does modify the global database state, that is just too much, at
> least at
> > > the present, to ask of SQL Server. Since it is impossible to absolutely
> > > determine in advance all cases when dynamic SQL will modify the global
> > > database state, currently SQL Server simply takes the easier path of
> > > disallowing dynamic SQL.
> > > -------------------------------------------
> > > BP Margolin
> > > Please reply only to the newsgroups.
> > > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
> which
> > > can be cut and pasted into Query Analyzer is appreciated.
> > > > Hello, Bruce!
> > > > Heh...as far as I can tell, it's not possible. But, I'm hopeful that
> one
> > > of
> > > > the SQL Gurus here will be able to help. :-)
> > > > The only context that I've been able to find that EXECUTE works in a
> > > > function is with the INSERT command. Now, in my case, in that I
> wanted to
> > > > return some information from the dynamic SQL, I thought that'd work
> like a
> > > > charm. However, I don't want to have to create a static table, and it
> > > > appears as if temporary tables aren't permitted in functions. Nor
> does
> > > > EXECUTE work with a table variable.
> > > > I'm not sure how else to execute dynamic SQL other than with the
> EXECUTE
> > > > statement. I wish there were a default extended stored procedure that
> > > > essentially did this. ;-)
> > > > > Interestingly, I needed to do the same thing today: build a string
> and
> > > > > EXECUTE(string) that string inside of a function. As far as I can
> tell
> > > > from
> > > > > BOL, you aren't allowed to call EXEC() from inside a function.
> Hopefully
> > > > I'm
> > > > > wrong...
> > > > > Bruce
> > > > > > Hello, BP!
> > > > > > Well...I took a peek at that section, but I'm still not getting
> it.
> > > > > Here's
> > > > > > what I would have done if I could have used temporary tables in
> this
> > > > > > function:
> > > > > > returns varchar(8000)
> > > > > > as
> > > > > > begin
> > > > > > create table #Value (Value varchar(8000))
> > > > > > drop table #Value
> > > > > > end
by