|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
EXEC('string') from a UDF??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 Hi
UDF's can contain certain functions like EXECUTE. Basic rule of the UDF. Regards -------------------------------- Mike Epprecht, Microsoft SQL Server MVP Zurich, Switzerland IM: m***@epprecht.net MVP Program: http://www.microsoft.com/mvp Blog: http://www.msmvps.com/epprecht/ Show quote "SteveInBeloit" <SteveInBel***@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 can´t execute q sqlstring in a UDF, try to code a stored prcoedure for
that. HTH, Jens Suessmeyer. Show quote "SteveInBeloit" <SteveInBel***@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') [shudder] Parameterizing table names is a very bad idea and with good design > > I use this in stored procs alot when the table name is a variable. 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 Show quote "David Portas" wrote: > > 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 > -- > > > 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,
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 need 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 Show quote "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 -- David Portas (REMOVE_BEFORE_REPLYING_dpor***@acm.org) writes:
> Local temp tables are scoped to a session anyway so there's no need to There may be plenty of reasons for this. Say for instance you result> parameterize the name. On the other hand, why are you using temp tables to > return data to the client? 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, esq***@sommarskog.se Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp Steve
Have tried adp of access where you can dynamically do all this stuff with sql server Regards R.D Show quote "Erland Sommarskog" wrote: > David Portas (REMOVE_BEFORE_REPLYING_dpor***@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, esq***@sommarskog.se > > Books Online for SQL Server SP3 at > http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp > >  |
|||||||||||||||||||||||
Other interesting topics