Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > Bugs & Issues> Incorrect number of resultsets for stored procedure
 

Pages: 1
Bugs & Issues
Incorrect number of resultsets for stored procedure
Page:1/1 

  Print all messages in this thread  
Poster Message
DaveR
User



Location:

Joined on:
15-Jun-2004 16:37:10
Posted:
43 posts
# Posted on: 03-Jan-2014 22:15:10.  
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.
  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8099 posts
# Posted on: 04-Jan-2014 00:15:23.  
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).


David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
DaveR
User



Location:

Joined on:
15-Jun-2004 16:37:10
Posted:
43 posts
# Posted on: 04-Jan-2014 17:55:54.  
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.
  Top
daelmo
Support Team



Location:
Guatemala City
Joined on:
28-Nov-2005 23:35:24
Posted:
8099 posts
# Posted on: 04-Jan-2014 19:37:08.  
Good you figured it out Wink

David Elizondo
LLBLGen'ing (articles and code snippets) | linkedin | twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37804 posts
# Posted on: 05-Jan-2014 12:25:31.  
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 Wink
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.