Designer in 5.5.3 creates multiple resultsets

Posts   
 
    
acl
User
Posts: 59
Joined: 28-Mar-2012
# Posted on: 26-Jul-2019 17:38:07   

Hi,

we're using the LLBLGen Designer Version 5.5.3 with MS SQL Server.

Consider the following stored procedure definition:

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE name = N'TestProcedure' AND type = 'P') DROP PROCEDURE TestProcedure GO CREATE PROCEDURE TestProcedure @iSomeParameter int AS SET NOCOUNT ON;

IF @iSomeParameter < 0 BEGIN SELECT @iSomeParameter C1, @iSomeParameter-1 C2, @iSomeParameter-2 C3 END ELSE BEGIN SELECT @iSomeParameter C1, @iSomeParameter+1 C2, @iSomeParameter+2 C3 END

We select this SP when syncing relational model data, and we also select it in the second step, to determine the resultset relational model data.

As a result, we get a relational model with one stored procedure and two result sets.

Why is that? The SP obviously only produces one result. However LLBLGen finds two result sets. Is it possible to get just one result set?

Thanks!

Attachments
Filename File size Added on Approval
Screenshot.png 5,167 26-Jul-2019 17:38.23 Approved
daelmo avatar
daelmo
Support Team
Posts: 8152
Joined: 28-Nov-2005
# Posted on: 27-Jul-2019 08:50:01   

LLBLGen run your SP with null parameters and obtain the info about the resulset: So it’s your sp which returns the duplicate: You colud try:

A. Set the Number of rsulset manually B. Modify your SP code to return a resulset

acl
User
Posts: 59
Joined: 28-Mar-2012
# Posted on: 28-Jul-2019 09:59:36   

Thanks, daelmo.

That's what I thought. However, the stored procedure above clearly produces just one resultset at any time. It is impossible for the SP to actually produce two resultsets. Or are you saying, that somehow SQL Server does not care about the IF and therefore there are two resultsets.

Btw we tried limiting the number of resultset manually in the designer using the context menu. This did not work. Clicking on "1" to limit to a single resultset did not have any impact.

daelmo avatar
daelmo
Support Team
Posts: 8152
Joined: 28-Nov-2005
# Posted on: 29-Jul-2019 07:50:36   

The ‘set the number of resulsets’ option is for set the number of resulsets manually (i.e.: you didn’t check Retreive Resulset option. Sorry if I didn’t explain it better.

The resulsets are called with the SCHEMAONLY clause, so indeed I think it’s ignoring the IF, however this is by design. Check this explanation: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=21521

acl
User
Posts: 59
Joined: 28-Mar-2012
# Posted on: 29-Jul-2019 14:54:23   

Thanks, daelmo, this worked!