Invoke SqlServer StoredProcedure

Hi, LightSpeed model is connected with MS SqlServer 2008 and has a link to a Stored procedure which has some parameters and a return value like this:

create procedure tmp.p_DoSomething
@par nvarchar(255),
@par2 money out,
@par3 nvarchar(255) out
as
-- do something
set @par3 = 'I did it.'
return 100
go

When I invoke this procedure, I don't get the return value.

var ret = uow.PDoSomething(par, out par2, out par3);

There ret is always equal -1. How can I get the return value and out parameters too?

Are you using a scalar procedure for this? If so this should be generating a return value parameter in the generated code (you can check this by opening your generated code file and looking for the procedure definition underneath your typed UnitOfWork declaration.

You can alternatively implement this manually on a partial class definition for your typed UnitOfWork.

Here is an example of what you would want:

public int PDoSomething(string par, ref decimal par2, ref string par3)
{
  int valueToReturn = -1;

  ProcedureParameter parParameter = new ProcedureParameter("par", par);
  ProcedureParameter par2Parameter = new ProcedureParameter("par2", par2, System.Data.ParameterDirection.InputOutput, System.Data.DbType.Decimal, null, null, null);
  ProcedureParameter par3Parameter = new ProcedureParameter("par3", par3, System.Data.ParameterDirection.InputOutput, System.Data.DbType.String, null, null, null);
  ProcedureParameter returnParameter = new ProcedureParameter("ret", valueToReturn, System.Data.ParameterDirection.ReturnValue, System.Data.DbType.Int32, null, null, null);
  ProcedureQuery procedureQuery = new ProcedureQuery("p_DoSomething", parParameter, par2Parameter, par3Parameter, returnParameter);
  Execute(procedureQuery);

  par2 = (decimal)(par2Parameter.Value);
  par3 = (string)(par3Parameter.Value);
  return (int)returnParameter.Value;
}

Jeremy

Thank you! You right.