I have an action sproc that always returns -1 even though the sproc itself is returning 0.

To try and figure this out I created a really simple sproc and it does the same thing:

ALTER PROCEDURE dbo.TestReturn (@testvalue int)

AS
    SET NOCOUNT ON
    
    RETURN 0

Running the above code in SQL Server 2005 returns:

Running [dbo].[TestReturn] ( @testvalue = 1 ).

No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [dbo].[TestReturn].

Running in LLBLGen as:

ActionProcedures.TestReturn(1);

Returns -1 and "A first chance exception of type 'System.InvalidOperationException' occurred in System.dll"

Why is it not returning 0 or what am I doing wrong?

Many Thanks, Joe

you have to call the overload which returns the returnvalue as a ref parameter. The one you're using returns the rows affected, which is always -1 as set nocount is set to on.

Ahh.

Thanks fors the info.

Joe

Frans:

I also was confused by this behavior but eventually understood the documentation. Is there a strong reason or preference for this approach? I'd much prefer to get the return value as the result of the ActionProcedures.xxx method. It seems to me that most people are returning a result code from the SP and checking that for a zero or non-zero result.

Speaking for myself, I always have NOCOUNT set to ON and I'm checking the rowcount inside the SP. If the rowcount is important to me, I make it available via an output parameter.

I'd be interested in hearing what other folks would prefer. Of course, if most people aren't using SP's, then it won't matter to them. I still find certain tasks that are easy to code as SP's and hard to code in LLBLGen. (But more are easier to do with LLBLGen )

Joel Reinford Data Management Solutions LLC

jovball wrote:

Frans:

I also was confused by this behavior but eventually understood the documentation. Is there a strong reason or preference for this approach? I'd much prefer to get the return value as the result of the ActionProcedures.xxx method. It seems to me that most people are returning a result code from the SP and checking that for a zero or non-zero result.

There are 2 values to return, sometimes (not always): the rowcount and the return value. In SQLServer for example, the returnvalue is a different value, it's not a real output parameter. So I opted for the value that's always there: the rowcount, and have the returnvalue be on one of the overloads.

Speaking for myself, I always have NOCOUNT set to ON and I'm checking the rowcount inside the SP. If the rowcount is important to me, I make it available via an output parameter.

This is not recommended if you're using entities, as you'll then not be able to save entities because the code checks if the # of rows affected is 0. If it is, the save failed (e.g. concurrency issues) and rolls back the transaction.

Frans:

Otis wrote:

Speaking for myself, I always have NOCOUNT set to ON and I'm checking the rowcount inside the SP. If the rowcount is important to me, I make it available via an output parameter.

This is not recommended if you're using entities, as you'll then not be able to save entities because the code checks if the # of rows affected is 0. If it is, the save failed (e.g. concurrency issues) and rolls back the transaction.

I wasn't clear in what I wrote earlier. I always set NOCOUNT ON inside my stored procedures only, not for the overall settings. This habit came partly from working with Crystal Reports (boo!, hiss!) which had a tendency to choke on the rowcount information.

How difficult would it be to put an option in LLBLGen for the SP methods to return either a) rowcount as current or b) return value as in SQL Server?

Joel Reinford Data Management Solutions LLC

jovball wrote:

Frans:

Otis wrote:

Speaking for myself, I always have NOCOUNT set to ON and I'm checking the rowcount inside the SP. If the rowcount is important to me, I make it available via an output parameter.

This is not recommended if you're using entities, as you'll then not be able to save entities because the code checks if the # of rows affected is 0. If it is, the save failed (e.g. concurrency issues) and rolls back the transaction.

I wasn't clear in what I wrote earlier. I always set NOCOUNT ON inside my stored procedures only, not for the overall settings. This habit came partly from working with Crystal Reports (boo!, hiss!) which had a tendency to choke on the rowcount information.

ieks! that sounds really bad, but then again, it's crystal reports, the most worst piece of erm... 'software' I ever had to work with.

How difficult would it be to put an option in LLBLGen for the SP methods to return either a) rowcount as current or b) return value as in SQL Server?

I think it's a matter of which template you use. If you want that behavior, you should alter the templates (use a copy, bind that to the templateID in a copy of the templateset config ) for actionprocedures and you're good to go.