MS SQL-possible to use with SQLDB-Stored Procedures, Inline -, Scalar function

[af0815]

Is it possible to use Stored Procedures, Inline functions, Scalar function (SQLdb with FreeTDS driver) ?

a) Stored Procedures: Can give back a mix of Resultset and Output params
As is understand SP can only one resultset (the first) and no output params. See https://forum.lazarus.freepascal.org/index.php/topic,32765.msg211589.html#msg211589
So it looks not realy useable, if you need calculated values back

b) Inline functions: Returns a Resultset
Should be possible, but only with open, not with ExecSQL

c) Scalar function: Returns a Value
How can i do it ?

Have anybody more information ?

[devEric69]

As is understand SP can only one resultset (the first) and no output params.

Concerning this point (sorry, I don't use SP in mySQL), but with other server databases, the output variables sets constitute the iterable recordset sent back towards the client program (client which will call "while not SP.eof do call SP.next and do stuff").
The difference is that in a SP, it's possible to use different kind of back-end variables like implicite or explicit cursors variables, input parameters variables, local variables, and output parameters. AFAIK, at the end of the server side data processing, it returns a {pseudo-}SELECT: the ouptut parameters sets, which are seen in the client with SP.fieldByName('abc').AsXyz.
That said SP.Execute should indeed return normally just\at most an row (Execute is normaly used to do an "action SQL" like an INSERT or an UPDATE). Some server databases can return a RETURNING clause). SP.Open (Open is normaly used to do an "selectable SQL" i.e. a {pseudo-}SELECT) should be called to iterate (fetch by fetch, i.e. row by row) each output variables set.

Disclaimer: I don't use SP in mySQL.

[af0815]

Disclaimer: I don't use SP in mySQL.

BTW: I don't talk about mySQL, i ask about MS SQL with SQLdb with the FreeTDS driver.

[devEric69]

Sorry .

[LacaK]

a) Stored Procedures: Can give back a mix of Resultset and Output params

https://wiki.lazarus.freepascal.org/mssqlconn#Output_parameters_of_stored_procedures

b) Inline functions: Returns a Resultset
Should be possible, but only with open, not with ExecSQL

Yes, as far as resultset is returned you must use Open ("select * from dbo.fnName(:param1)")

c) Scalar function: Returns a Value
How can i do it ?

Open ("select dbo.fnName(:param1)")

[devEric69]

b) Inline functions: Returns a Resultset
Should be possible, but only with open, not with ExecSQL

Yes, as far as resultset is returned you must use Open ("select * from dbo.fnName(:param1)")

Apparently, the return of multiple records with an MS-SQL SP is done - at most simple - like this (using Open on the client's side):

Code: MySQL  [Select][+][-]

1. Create Procedure doStuff(@idFoo Integer, ...);
2. As
3.   SELECT idFoo, ...
4.   FROM tblFoo
5.   WHERE (idFoo=@idFoo) AND
6.               (...)
7.