How to used mapped stored procedures (MS SQL)

Posts   
 
    
tkelley66
User
Posts: 16
Joined: 16-Apr-2013
# Posted on: 01-Feb-2018 18:58:59   

I have created a new project and mapped the tables/stored procedures. How do I call the sp from my C# program?

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 01-Feb-2018 20:06:45   

Did you map them to SP Calls, to TypedViews?

For the first, please check Calling a Stored Procedue

For the later please check Instantiating a TypedView mapped onto a Stored Procedure Resultset

tkelley66
User
Posts: 16
Joined: 16-Apr-2013
# Posted on: 01-Feb-2018 20:28:24   

I mapped them to SPCalls (right click them in catalog view and selected Create Stored Procedure Call Definitions). The all appear under Stored Procedure Calls in the project view. If I try to type RetrievalProcedures., I don't see any of the stored procedures.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 01-Feb-2018 21:13:06   

Did you make sure you retrieved the resultsets during the catalog meta-data retrieval? It's a page in the wizard which lists all stored procedures you've selected from the catalog and you have to specify which ones you want the designer to retrieve the resultset for. If you don't do that, all procs are seen as 'action' stored procedures.

If I try to type RetrievalProcedures., I don't see any of the stored procedures.

I don't know what you mean, where did you type this?

Frans Bouma | Lead developer LLBLGen Pro
jovball
User
Posts: 434
Joined: 23-Jan-2005
# Posted on: 02-Feb-2018 14:57:15   

I strongly prefer not getting the resultsets during the meta-data retrieval and assigning the resultsets in the designer via catalog explorer. That avoids any data changes caused by executing the stored procedures.

Right-click on the procedure and select "Set Number of Resultsets." Usually it would be 1. If it is 0, the call will be created as an ActionProcedure rather than a RetrievalProcedure and you won't get a resultset.

I think you have to assign the resultset count prior to creating the call definition but Frans could confirm that.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# Posted on: 02-Feb-2018 16:56:13   

Indeed one should set the ResultSet count in the Catalog Explorer to a count more than 0 to be seen as a RetreivalProcedure rather than an ActionProcedue, then map the SP to SPCall.

tkelley66
User
Posts: 16
Joined: 16-Apr-2013
# Posted on: 02-Feb-2018 17:48:55   

I did not specify which stored procedures to retrieve the result set for. After re-creating the project it worked fine.

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 02-Feb-2018 22:00:21   

jovball wrote:

I strongly prefer not getting the resultsets during the meta-data retrieval and assigning the resultsets in the designer via catalog explorer. That avoids any data changes caused by executing the stored procedures.

Right-click on the procedure and select "Set Number of Resultsets." Usually it would be 1. If it is 0, the call will be created as an ActionProcedure rather than a RetrievalProcedure and you won't get a resultset.

I think you have to assign the resultset count prior to creating the call definition but Frans could confirm that.

That's correct, and it's an alternative way to get retrieval procs indeed, thanks for the heads up simple_smile

Retrieving the resultset shapes can be beneficial tho, you can define typedviews on them, but if that's not your goal, the change to the # of resultsets for a proc is good enough simple_smile

Frans Bouma | Lead developer LLBLGen Pro