1

I'm trying to search all tables and columns in a database, a la here. 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 here. Again, EXEC is used to execute "dynamic SQL".)

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:

Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

According to the answer to this post, apparently by a Microsoft developer, 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.

So... is there any way to iterate over many tables/columns within a function?

I see from BOL that

The following statements are valid in a function: ...

  • EXECUTE statements calling extended stored procedures.

Huh - How could extended SP's be guaranteed side-effect free?

But that doesn't help me anyway:

The extended stored procedure, when it is called from inside a function, cannot return result sets to the client. 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.

Since we need the function to return the results of the search, an ESP won't help.

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.

I can think of a few solutions right now, none of which is very satisfactory:

  • 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,
  • 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,
  • 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.

Thanks for any suggestions.

P.S. Upon further searching, I stumbled across this question which is closely related. It has no answers.

Update: No longer needed

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.

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.

Improve this question
6
  • P.S. This Q/A basically says "you can't get there from here": stackoverflow.com/questions/2461482/… Commented Dec 9, 2011 at 21:52
  • 1
    Why do you need a function? Could you use a stored procedure with an output parameter instead? Commented Dec 9, 2011 at 23:20
  • I 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... Commented Dec 9, 2011 at 23:28
  • @MikaelEriksson: good Q. The reason was that I couldn't get our web app framework / JDBC driver to retrieve row results from an SP. However we seem to have overcome that problem... we are using the SP and getting results from it. Commented Dec 12, 2011 at 18:15
  • @Tony: can you give an example of what you mean by "fire the guts of the SP as a parameterized query"? I think I understand, but ... feel free to use an answer to give yourself room to explain. Regarding efficiency, I'd be happy to see an example of a more efficient implementation. I'm not a SQL guru so I'm happy to get something that works. Commented Dec 12, 2011 at 18:20

1 Answer 1

Reset to default
1

An sp is basically a predefined sql statment with some add ons.

So if you had PSEUDOCODE

Create SP_DoSomething As
  Select * From MyTable
END

And you can't use the SP

Then you just execute the SQL as in "Select * From MyTable"

As for that naff sql code. For start you could join table to column with a where clause, which would get rid of that line by line if stuff.

Ask another question. Like How could this be improved, there's lots of scope for more attempts than mine.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for explaining further. That is what I thought you meant.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.