serhiy.zvedenyuk wrote:

W\ a breaking change in v.3.0 as "Stored procedure call code no longer has overloads which accept/return ReturnValue values." I can no longer access value returned from SP's via ExecuteScalar style. With a restriction of no changing SP's it makes the usage of LLBLGen 3.0 mapper hardly feasible or am I missing something? Any ideas? Maybe some custom code-generation templates exist for this case? Quick answer much appreciated.

Walaa wrote:

An action stored procedure can have a return value returned by the ado.net thet might represent affected rows, this is something else than input oroutput parameters.

The generated method calls that call th SP, will return the ReturnValue, and will accept parameters for the Input and output parameters of the SP. In previous versions we had an overload of these methods that would take the a parameter for the ReturnValue, this has been removed. So you are left with the methods that return the ReturnValue.

Next time please start a new thread.

For the sample SP like CREATE PROCEDURE [dbo].[ProcessGoods] @identifier int AS BEGIN

SET NOCOUNT ON;

return 1 --lets always return one END sample old code targeting 2.0 LLBLGen

ActionProcedures.ProcessGoods(identifier, ref result);

new code (3.0)

res = ActionProcedures.GetDatabaseLock(identifier);

and I'm aware that res in this case will be returning a number of affected rows and not a result of ExecuteScalar-like call. Is there any possibility to return the ReturnValue without changing the SP interface signature (introducing an extra parameter for the result)?

A tiny update. 2 Otis: Suppose I do understand what you mean as a solution to the situation. I believe it's something like modifying the stored procedure as introducing a superfluous output parameter e.g. ALTER PROCEDURE [dbo].[ProcessGoods] @intGoodsID int = 0, @result int out --just to force generator to create an ref parameter AS BEGIN

SET @result = 1;

-- RETURN -- no return is required/processed anymore

END

and then the signature of 2.6 generated wrapper will remain unchanged.

Is that the only solution?

BTW. In SP call editor I still can see the "ReturnValue" option for direction (besides Input, Output, InOut), is that just rudimentary and cannot be used?

Thanks ahead!

-S.

serhiy.zvedenyuk wrote:

A tiny update. 2 Otis: Suppose I do understand what you mean as a solution to the situation. I believe it's something like modifying the stored procedure as introducing a superfluous output parameter e.g. ALTER PROCEDURE [dbo].[ProcessGoods] @intGoodsID int = 0, @result int out --just to force generator to create an ref parameter AS BEGIN

SET @result = 1;

-- RETURN -- no return is required/processed anymore

END

and then the signature of 2.6 generated wrapper will remain unchanged.

Is that the only solution?

It's the preferred solution, as it's uniform and standarized: output of a proc is always through output parameters, and it works for all types (as return values are int only)

BTW. In SP call editor I still can see the "ReturnValue" option for direction (besides Input, Output, InOut), is that just rudimentary and cannot be used? Thanks ahead! -S.

It's a glitch, we have all values there, 'returnvalue' is no parameter type which is recognized in the meta-data. In v2.x templates, it was added as a special parameter regardless if the proc did use a return statement or not, as you can do that with an Int32 parameter called 'RETURNVALUE'. MS' Books Online suggests return values are used for signaling failure/success (0 is success, 1 is failure), not for returning actual values, use output parameters for that.

So if there's a way to change the proc, I'd absolutely do it. If you're stuck with the signatures of the procs involved (I don't think there are that many, considering the fact returnvalue is bound to be an int), there is a way to get this working, but we won't add it to the main templates.

It works by adding a parameter to the set of parameters for the proc called RETURNVALUE, being an int and having a direction of ReturnValue. when the proc is called, this parameter is automatically set with the value returned. There's an include template, called proceduresInclude.template which builds the Create...call methods. There, the returnvalue parameter should be added. (as the last parameter)

Then in the proc template (e.g. actionProceduresAdapter.template), you have to add extra code to the method block to return the return value as a ref. I'd do this in a new method using the same method template code as the one currently already there, but with an extra int32 ref parameter for the output parameter.

This only makes sense for sqlserver btw. But, if it's possible, change the proc signatures instead.