hmstechsupport
asked on
Return a table/result set from a SQL Server to Delphi
Hello,
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?
Thanks,
Edward
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?
Thanks,
Edward
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
(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
ASKER
There does not seem to be an appropriate answer other than the one we provided ourselves. If required please delete the question.
http://www.scip.be/index.p
http://www.berenddeboer.ne
I propose to call execute immediate inside sql procedure to execute update as string parameter:
Open in new window
http://www.experts-exchang