Databases
StackOverflow2013
Postsdbo
POvariable table or column names in a function
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POvariable table or column names in a function
    primarykey
    data
    text
    <p>I'm trying to search all tables and columns in a database, a la <a href="http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm" rel="nofollow noreferrer">here</a>. The suggested technique is to construct SQL query strings and then EXEC them. This works well, as a stored procedure. (Another example of variable table/column names is <a href="https://stackoverflow.com/questions/3059019/variable-table-or-column-names">here</a>. Again, EXEC is used to execute "dynamic SQL".)</p> <p>However, my app requires that I do this in a function, not an SP. (Our development framework has trouble obtaining results from an SP.) But in a function, at least on SQL Server 2008 R2, you can't use EXEC; I get this error:</p> <pre><code>Invalid use of a side-effecting operator 'INSERT EXEC' within a function. </code></pre> <p>According to the answer to <a href="http://connect.microsoft.com/SQLServer/feedback/details/511080/insert-exec-with-local-table-variable-in-function" rel="nofollow noreferrer">this post, apparently by a Microsoft developer</a>, this is by design; it has nothing to do with the INSERT, only the fact that when you execute dynamically-constructed SQL code, the parser cannot guarantee a lack of side effects. Therefore it won't allow you to create such a function.</p> <p>So... is there any way to iterate over many tables/columns within a function?</p> <p>I see from <a href="http://msdn.microsoft.com/en-us/library/ms186755.aspx" rel="nofollow noreferrer">BOL</a> that </p> <blockquote> <p>The following statements are valid in a function: ...</p> <ul> <li>EXECUTE statements calling extended stored procedures.</li> </ul> </blockquote> <p>Huh - How could extended SP's be guaranteed side-effect free?</p> <p>But that doesn't help me anyway: </p> <blockquote> <p>The extended stored procedure, when it is called from inside a function, <strong>cannot return result sets to the client</strong>. Any ODS APIs that return result sets to the client will return FAIL. The extended stored procedure could connect back to an instance of SQL Server; however, it should not try to join the same transaction as the function that invoked the extended stored procedure.</p> </blockquote> <p>Since we need the function to return the results of the search, an ESP won't help.</p> <p>I don't really want to get into extended SP's anyway: incrementing the number of programming languages in the environment would complicate our development environment more than it's worth.</p> <p>I can think of a few solutions right now, none of which is very satisfactory:</p> <ul> <li>First call an SP that produces the needed data and puts it in a table, then select from the function which merely reads the result from the table; this could be trouble if the search takes a while and two users' searches overlap. Or,</li> <li>Have the application (not the function) generate a long query naming every table and column name from the db. I wonder if the JDBC driver can handle a query that long. Or,</li> <li>Have the application (not the function) generate a long series of short queries naming every table and column name from the db. This will make the overall search a lot slower.</li> </ul> <p>Thanks for any suggestions.</p> <p>P.S. Upon further searching, I stumbled across <a href="https://stackoverflow.com/questions/8083563/using-dynamic-sql-in-user-defined-function-to-return-string-not-modify-data">this question</a> which is closely related. It has no answers.</p> <h2>Update: No longer needed</h2> <p>I think this question is still valid, and we may again have a situation where we need it. However, I don't need an answer anymore for the present problem. After much trial-and-error I managed to get our application framework to retrieve row results from the RDBMS via the JDBC driver from the stored procedure. Therefore getting the thing to work as a function is unnecessary.</p> <p>But if anyone posts an answer here that helps with the stated problem, I will be happy to upvote and/or accept it as appropriate.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USLarsH
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POvariable table or column names in a function
      1. This table or related slice is empty.
      1. VTUpMod
    1. COP.S. This Q/A basically says "you can't get there from here": http://stackoverflow.com/questions/2461482/dynamic-access-to-tables-from-another-database-inside-an-user-function
      singulars
      1. POvariable table or column names in a function
      1. USLarsH
    2. COWhy do you need a function? Could you use a stored procedure with an output parameter instead?
      singulars
      1. POvariable table or column names in a function
      1. USMikael Eriksson
    3. COI might be missing something, but why not just fire the guts of the stored proc as a parameterised query. Having it as stored proc is nice, but there's nothing in it that can only be execute from stored proc. I'd have used a cursor as well. I know it's naughty, but that code for getting table and column nme is wildly inefficient, I was massively unsurprised when informmed it would be a bit slow...
      singulars
      1. POvariable table or column names in a function
      1. USTony Hopkinson
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload