Multi-line SQL statements

[GMG]

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!

[Dimon]

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

[GMG]

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