Devart Forums

Hello,

Do you know if there is a way to submit multi-line SQL statements via dbExpress for MSSql? We have some SQL code that is currently submitted as DataSet.Command text, along the lines of:

declare @output_var...
exec [storedprocedure @output_var]
select @output_var

When doing this using Devart DBExpress I get an error: "InternalDataset: Cursor not returned from query."

Are compound statements like this possible? Any recommended workarounds if no?

Thanks!

To solve the problem use the SQLDataSet.ExecSQL method to execute SQL query.

Hi Dimon,

Thanks for the info. It looks like we can get by with simple stored procedures after all.

A follow-up question: I see how to execute a stored procedure and get a value out using input and output parameters, but so far see no way to execute an arbitrary stored procedure dynamically that returns a result set or a scalar value.

Do you know of any way to accommodate dynamic SQL strings that execute arbitrary stored procedures with return values on the fly, like:

select * from stored_procedure_name param1, param2

or

return_value = exec stored_procedure_name param1, param2

Supposedly the Embarcadero drivers support the former syntax, although both those and DevArt do not recognize the stored procedure name as a valid object for a select statement, using either command types of ctQuery or ctStoredProcedure in SqlDataSet (invalid object), or by using TSqlStoredProc (cursor not returned).

Thanks very much for any tips!!

Gordon