• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2062
  • Last Modified:

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?


  • 2
1 Solution
Sinisa VukCommented:
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.
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The Lifecycle Approach to Managing Security Policy

Managing application connectivity and security policies can be achieved more effectively when following a framework that automates repeatable processes and ensures that the right activities are performed in the right order.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now