How can I get the return value of an SQL SP from C/AL

kamel.hedi · 2006-02-15

Hi All,

Does someone know how can I get the return value of an SQL stored procedure from C/AL Code (Navision) : I have to call the SP (it's done), but i must read the value returned by the SP to show informative messages to the user.

Thanks

DenSter · 2006-02-15

Not directly, but you could have your SP write to a table, and then have Navision read the table after the SP is done, through a linked view, or directly into a Navision table.

kamel.hedi · 2006-02-15

The problem is that it's not my SP (I haven't developed it), i have to call it simply to update data in another SQL database : When calling the SP, i do it with parameters that i collect from Navision DB to update the other SQL DB : but i don't need to write in Navision and i want to show to the user if the data are updated or not for example.

CREATE (adoconnection);
CREATE("ado RS");
sSql := 'SP_NAME' + ' ' + var1 + ','+ var2; //
adoconnection.ConnectionString := 'Driver= {SQLServer};
Server=MyServer;Database=MyDB;Uid=user;Pwd=uesrpwd;';
adoconnection.Open();
"ado RS" := adoconnection.Execute (sSql);//Update the Other SQL DB
//Here : can i read the return value
adoconnection.Close;

ara3n · 2006-02-15

Create a view in navision database into the other database, and read the data from that other database, and give the user the correct message.

kamel.hedi · 2006-02-15

How can I create a view in navision DB ??

Syntax ??

DenSter · 2006-02-15

No you create a SQL Server view in Enterprise Manager, in your Navision database that looks into the other database. Then you create a linked table in Navision that displays the data in that view, so you can read it from within Navision. How to do this is described in the application designer guide, which is in the Doc folder of your Navision product CD.

It is possible to use ADO in the C/AL editor, so it might also be possible to see the return value of the SP. I haven't done anything like that though, maybe someone with ADO experience can give you more details.

ara3n · 2006-02-15

If the Storedprocedure returns a recordset then yes you can use ADO to retrieve the recordset and look at the data. If the Storeprocedure doesn't return a recordset, then creating the view is the way to go.

aohlfsen · 2006-02-15

Don't know if this works with ado returning records but i used to call a "non returning" sp with ADO and retrieved the return value from the sp in the ADO parameter structure, something like this

IF ISCLEAR(ADOConn) THEN
CREATE(ADOConn);

ADOConn.Open(ConnectionString);

IF ISCLEAR(ADOCommand) THEN
CREATE(ADOCommand);

ActiveConnection:=ADOConn;

ADOCommand.ActiveConnection:=ActiveConnection;
ADOCommand.CommandTimeout:=0;
ADOCommand.CommandType:=4;

IF ISCLEAR(ADOParameter) THEN
CREATE(ADOParameter);

ADOParameters:=ADOCommand.Parameters;

ADOParameter:=ADOCommand.CreateParameter('@RetVal',3,4,0);
ADOParameters.Append(ADOParameter);

ADOCommand.CommandText:='Stored procedure to call'

ADOCommand.Execute;

The first parameter in the parameter collection will hold the return value of the SP.

r44712 · 2006-11-23

aohlfsen wrote:

Don't know if this works with ado returning records but i used to call a "non returning" sp with ADO and retrieved the return value from the sp in the ADO parameter structure, something like this

IF ISCLEAR(ADOConn) THEN
CREATE(ADOConn);

ADOConn.Open(ConnectionString);

IF ISCLEAR(ADOCommand) THEN
CREATE(ADOCommand);

ActiveConnection:=ADOConn;

ADOCommand.ActiveConnection:=ActiveConnection;
ADOCommand.CommandTimeout:=0;
ADOCommand.CommandType:=4;

IF ISCLEAR(ADOParameter) THEN
CREATE(ADOParameter);

ADOParameters:=ADOCommand.Parameters;

ADOParameter:=ADOCommand.CreateParameter('@RetVal',3,4,0);
ADOParameters.Append(ADOParameter);

ADOCommand.CommandText:='Stored procedure to call'

ADOCommand.Execute;

The first parameter in the parameter collection will hold the return value of the SP.

Maybe I'm being stupid, but how is ADOParameters declared? I tried

'Microsoft ActiveX Data Objects 2.8 Library'.Parameters as an Automation variable, but when I tried to declare an 'instance' of it:

IF ISCLEAR(ADOParameters) THEN BEGIN
  IF NOT CREATE(ADOParameters) THEN ERROR('Cannot create ADO Parameters automation variable.');
END;

I got the error that it could not be created??? :-s

Thanks for any input!

Phenno · 2006-11-24


MAdo.Properties().Item('Data Source').Value := FORMAT(ServerName);
MAdo.Properties.Item('Initial Catalog').Value := FORMAT(DBName);

MAdo.Open('',UserName,PassWord);
IF MAdo.State = 0 THEN ERROR(ErrorText);

var_aConnection:= MAdo;
Comm.ActiveConnection:=var_aConnection;

Comm.CommandText := 'spName';
Comm.CommandType := 4;
Comm.CommandTimeout := 0;

ParametarV:=Comm.CreateParameter('@OutputVar1', 200, 2, 30,YourLocalVar1);
Comm.Parameters.Append(ParametarV);

ParametarV:=Comm.CreateParameter('@OutputVar2', 3, 2, 0,YourLocalVar2);
Comm.Parameters.Append(ParametarV);


Comm.Execute;

YourLocalVar1 := FORMAT(Comm.Parameters.Item('@OutputVar1').Value);
EVALUATE(YourLocalVar2, FORMAT(Comm.Parameters.Item('@OutputVar2').Value));

though "YourLocalVars" has been initialized twice, only with

YourLocalVar1 := FORMAT(Comm.Parameters.Item('@OutputVar1').Value);

I get the value... first mentioning of that var is just cause of problem with compiling/running it...

r44712 · 2006-11-27

BlackTiger wrote:

I got the error that it could not be created???

You don't need to instantiate this variable.

So what is it created as - me thoroughly confused???

Is it text, code, what????

:-s :-k

If its anything other than 'Automation' (e.g. Text) I get the error that a AdoParameters cannot equal Text (which is a valid statement)?

And if its not declared at all 'Please define ADOParameters in C/AL Globals' (which is to be expected)

How can I get the return value of an SQL SP from C/AL

Comments

Categories