Return a table/result set from a SQL Server to Delphi


We're attempting to return a table/result set from a SQL Server function or stored procedure into a Delphi application. We're currently attempting this from a table valued function which works up until the point where we need to update another tables' data using an UPDATE SQL statement. The error message is 'Invalid use of side effecting operator 'UPDATE' within a function' which is a limitation of SQL Server functions. If we use a stored procedure instead how could we return a table/result set back to the calling Delphi application. We've considered using a temp table as in passing the temp table name as a parameter from the Delphi application to the SQL stored procedure thereby making the temp table result set accessible from the Delphi application. Does this seem feasible? Are there any alternate methods we can employ?


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sinisa VukSoftware architectCommented:
Great article which can help you:
I propose to call execute immediate inside sql procedure to execute update as string parameter:
execute immediate "update ...."

Open in new window
hmstechsupportAuthor Commented:
Thanks, but we really do not want to write dlls for Delphi.  Realistically we are limited to calling either a stored procedure or function.  We realize that we can do it with temp tables, or we could write Delphi code to replace the t-sql, but the goal is to do it on the database server and return a table/dataset.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Geert GOracle dbaCommented:
from what i remember for Delphi and SQL Server
(it's a bit fuzzy as it's over 10 years ago).

last statement in your stored procedure should be the select statement for the data you want returned
also, call the open on the stored procedure, not the execute

i see sinisav has posted links to very extensive documentation.
you might have missed the bottomline with all the docu

when you need to UPDATE and SELECT ...
first run the update, returning a success or not value

then run the select
or the next procedure with the select
hmstechsupportAuthor Commented:
There does not seem to be an appropriate answer other than the one we provided ourselves.  If required please delete the question.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.