Using LLBLGen Pro 4.1 (11/26/2013).

We recently made a change to a stored procedure to simplify the FROM clause in a dynamically generated query to join fewer tables. The changes seem benign, but now when refreshing the model, the schema shows 2 resultsets for this SP even though it only returns one. The Catalog Explorer shows two instances of "Resultset1" with identical fields.

To attempt to resolve this, I right-click the SP and choose "Set number of resultsets"->1. However, this does not do anything. The SP still shows 2 resultsets, and the generated code uses a DataSet instead of DataTable.

To resolve this problem, I have to revert to the older version of the SP and then refresh the model.

I can not figure out why LLBLGen sees 2 resultsets from the SP. In any case, it seems like a bug that the "Set number of resultsets" menu does not function.

Setting the number of resulsets manually maybe doesn't work in your case because when you refresh your DB, you selected that SP in the step to retrieve the resulsets. As I see you have two options here:

A. If you are not using the resulsets to reverse mapping TypedViews, then you can just not selecting that SP in the step when you retrieve the resulsets, then go and change the number of resulsets manually as you did.

B. If you are using the resulsets, you might want to check your SP code to see why it's returning two resulsets. You can put the sql code here so we can evaluate it (include the relevant table creation). You could open a new HelpDesk thread for that (which is private).

The SP never returns two resultsets, but I think we figured out why LLBLGen was confused.

We made a tweak to the logic and now it properly determines that there is one resultset.

Originally the logic was:

IF ... BEGIN SET @sql = ... EXEC(@sql) RETURN END -- Alternate path SET @sql = ... EXEC(@sql)

We modified the logic as follows:

IF ... BEGIN SET @sql = ... END ELSE BEGIN -- Alternate path SET @sql = ... END EXEC(@sql)

Now it works as expected.

Good you figured it out

Keep in mind that procs like this can be slow as the if statement forces the optimizer in sql server to recompile the proc to an execution plan at every execution. If it's a simple flag on a parameter, you could decide to move that if statement to your code, calling one proc or the other based on state in the application, avoiding the recompile. It might be you don't notice the recompile, but if the proc seems slow (e.g. it's called a lot) you now know the reason