Retrieval Procedure becomes Action Procedure

Posts   
 
    
hotmail
User
Posts: 47
Joined: 06-Feb-2013
# Posted on: 24-Jul-2015 05:19:15   

Using sql server 2014, asp.net MVC 4,LLBL v4.2 may 5th 2015 release.

I have a basic sp for server side paging, if i comment out the following code, it shows as retrieval sp with all the desired columns as result set, but if i keep it then it becomes action procedure.


                OFFSET @OffsetRows ROWS
FETCH NEXT @FetchRows ROWS ONLY

is FETCH NEXT not supported? how can i resolve it in making retrieval procedure?

here is the full SP.


ALTER PROCEDURE dbo.SpPayeeOrganizationSearch
    (
      @SearchValue NVARCHAR(50) ,
      @ColumnName NVARCHAR(25) ,
      @PersonId INT ,
      @OffsetRows INT ,
      @FetchRows INT
    )
AS
    BEGIN
    

        SET NOCOUNT ON;
        CREATE TABLE #organization ( OrganizationId INT )
    
        IF ( @ColumnName = 'OrganizationName' )
            BEGIN       
                INSERT  #organization
                        ( OrganizationId
                        )
                        SELECT  o.OrganizationId
                        FROM    dbo.Organization o
                        WHERE   o.OrganizationName LIKE '%' + @SearchValue
                                + '%'
            END 
        
        IF ( @ColumnName = 'DepartmentName' )
            BEGIN       
                INSERT  #organization
                        ( OrganizationId
                        )
                        SELECT  o.OrganizationId
                        FROM    dbo.Organization o
                        WHERE   o.DepartmentName LIKE '%' + @SearchValue + '%'
            END 

            
        SELECT  vso.*
        FROM    dbo.VwPayeeOrganization AS vso
                INNER JOIN #organization AS o ON vso.OrganizationId = o.OrganizationId
        ORDER BY vso.OrganizationName
                OFFSET @OffsetRows ROWS
FETCH NEXT @FetchRows ROWS ONLY

        DROP TABLE #organization
    
    END


Thanks

daelmo avatar
daelmo
Support Team
Posts: 8245
Joined: 28-Nov-2005
# Posted on: 24-Jul-2015 08:25:26   

LLBLGen retrieves the metadata calling the SP with null parameters. If it works documenting those lines, you should do it to fetch the metadata, then you could uncomment those.

David Elizondo | LLBLGen Support Team