Blog on software engineering
SQL Server Stored Procedure with temp table select, does not return correct result in LLBLGen Pro

SQL Server Stored Procedure with temp table select, does not return correct result in LLBLGen Pro

When you create a stored procedure like:

if exists (select 1 from sysobjects where name = 'GetDataFromTempTable' and xtype = 'p')
    drop procedure [dbo].[GetDataFromTempTable]
go

create procedure [dbo].[GetDataFromTempTable]
as
begin

-- Create temp table
create table #MyTempTable
(
    [Id] int not null primary key,
    [Nummer] varchar(10) not null
)

insert #MyTempTable ([Id], [Nummer]) values (1, 'Test1')

-- Return all rows from the temp table
select
    *
from
    #MyTempTable

end
go

 

and generate code with LLBLGen Pro to call this stored procedure,
it will generate an Action Stored Procedure Call instead off a Retrieval Stored Procedure Call.
This is, because LLBLGen Pro by default automatically generates the return type. see
http://www.llblgen.com/documentation/2.6/using%20the%20designer/designer_creatingproject.htm

By changing the user preference AutoDetermineSProcType to false, you can manually change the amount of result sets during the catalog refresh.

Change AutoDetermineSProcType
LLBLGen Pro > File > Preferences… > AutoDetermineSProcType > set to false

Refresh catalog
LLBLGen Pro > Project > Refresh All Catalogs… > Manually select the stored procedures to retrieve
> New number of Resultsets for selected procedures > set to 1

The stored procedure can now be added to Retrieval Stored Procedure Calls instead of Action Stored Procedure Calls.

3 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.