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