EXEC('string') from a UDF?

Can I not use an EXEC command from with in a UDF? I am looking through Book
s
online, but can't find anything that says I can't.
In my UDF I have
Exec('Select fields fromTABLE Where this=that')
I use this in stored procs alot when the table name is a variable.
When trying to do it from a UDF, I get:
"Invalid use of 'EXECUTE' within a function"
Thanks,
SteveHi
UDF's can contain certain functions like EXECUTE. Basic rule of the UDF.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:915E2719-E431-454E-89B3-A8B4802EC390@.microsoft.com...
> Can I not use an EXEC command from with in a UDF? I am looking through
> Books
> online, but can't find anything that says I can't.
> In my UDF I have
> Exec('Select fields fromTABLE Where this=that')
> I use this in stored procs alot when the table name is a variable.
> When trying to do it from a UDF, I get:
> "Invalid use of 'EXECUTE' within a function"
> Thanks,
> Steve|||You cant execute q sqlstring in a UDF, try to code a stored prcoedure for
that.
HTH, Jens Suessmeyer.
"SteveInBeloit" <SteveInBeloit@.discussions.microsoft.com> wrote in message
news:915E2719-E431-454E-89B3-A8B4802EC390@.microsoft.com...
> Can I not use an EXEC command from with in a UDF? I am looking through
> Books
> online, but can't find anything that says I can't.
> In my UDF I have
> Exec('Select fields fromTABLE Where this=that')
> I use this in stored procs alot when the table name is a variable.
> When trying to do it from a UDF, I get:
> "Invalid use of 'EXECUTE' within a function"
> Thanks,
> Steve|||> Exec('Select fields fromTABLE Where this=that')
> I use this in stored procs alot when the table name is a variable.
[shudder] Parameterizing table names is a very bad idea and with good design
it shouldn't be necessary. Why would you want to do this in a function
anyway? (BTW, you can't)
David Portas
SQL Server MVP
--|||Thanks for all the responses.
I use a UDF so I can use it to return a table variable to base a MS ACCESS
form off of. The table name is passed in cause it is a ##table, and will be
different for different users. The UDF gathers info from different tables,
including the ##table, then puts it all in a table variable to return to the
form.
Steve
"David Portas" wrote:

> [shudder] Parameterizing table names is a very bad idea and with good design
> it shouldn't be necessary. Why would you want to do this in a function
> anyway? (BTW, you can't)
> --
> David Portas
> SQL Server MVP
> --
>
>|||Local temp tables are scoped to a session anyway so there's no need to
parameterize the name. On the other hand, why are you using temp tables to
return data to the client?
David Portas
SQL Server MVP
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Local temp tables are scoped to a session anyway so there's no need to
> parameterize the name. On the other hand, why are you using temp tables to
> return data to the client?
There may be plenty of reasons for this. Say for instance you result
set includes a column that is an expression. Assume further that you
also use the recordset client-side to keep new and updated data, and
that you write data back through stored procedures. Problem is that
this field becomes read-only if you are in ADO. So our application
has it's fair share of temp tables to work around this brain-deadness
in ADO.
And then there are of course plenty of procedures where a temp table
is used as a work table, and this is where data is returned from.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||David,
Say the ACCESS application deals with orders. You enter the order section,
in the background, the code takes your userid and gathers lots of info from
different places and puts it into ##DavidTable. Your ACCESS form is based
off that table. When you leave that section, you apply any updates from the
temp table to the normal data structures. While in the section, you may nee
d
a report. The UDF reads the ##useridTable and creates a table variable with
info from that, and from other sources. Say another user (me) gets in while
you are there to work with orders also, they will get ##SteveTable to work
with.
That is what I am working with.
Thanks
"David Portas" wrote:

> Local temp tables are scoped to a session anyway so there's no need to
> parameterize the name. On the other hand, why are you using temp tables to
> return data to the client?
> --
> David Portas
> SQL Server MVP
> --
>
>|||If you use local temp tables instead of global (prefix with a single #
instead of ##) then the table is scoped to the connection. That way you can
use the same name for each user and you won't have to parameterize the name.
In ADO.NET you could use a disconnected recordset for this but I don't know
what other options exist in Access. I suspect there ought to be a method not
using temp tables. You might want to ask the question in Access forum.
David Portas
SQL Server MVP
--|||Steve
Have tried adp of access where you can dynamically do all this stuff with
sql server
Regards
R.D
"Erland Sommarskog" wrote:

> David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> There may be plenty of reasons for this. Say for instance you result
> set includes a column that is an expression. Assume further that you
> also use the recordset client-side to keep new and updated data, and
> that you write data back through stored procedures. Problem is that
> this field becomes read-only if you are in ADO. So our application
> has it's fair share of temp tables to work around this brain-deadness
> in ADO.
> And then there are of course plenty of procedures where a temp table
> is used as a work table, and this is where data is returned from.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>