USP_DATALIST_ACTION_ITEM

CREATE procedure dbo.USP_DATALIST_ACTION_ITEM 
    (@ACTIONITEMID uniqueidentifier = NULL,
     @FOLDERID int = null,
     @SORTPARAMETER varchar(100) = 'NAME', -- NAME or STATUS or TYPE

     @SORTORDER varchar(50) = 'ASC',      -- ASC or DESC

     @PAGENUMBER int = 1,
     @PAGESIZE int = 50
) with execute as owner
as
    set nocount on;

    DECLARE @sqlCommand nvarchar(4000)
    DECLARE @ParmDefinition nvarchar(1000)
    DECLARE @STARTROW int
    DECLARE @ENDROW int

    SET @STARTROW = ((@PAGENUMBER * @PAGESIZE) - @PAGESIZE + 1)
    SET @ENDROW = (@PAGENUMBER * @PAGESIZE)

    IF ISNULL(@STARTROW,-1) < 0
    SET @STARTROW = 1

    IF ISNULL(@ENDROW,0) = 0
    SET @ENDROW = 10000

    SET @ParmDefinition = '@ACTIONITEMID uniqueidentifier, @FOLDERID int, @SORTPARAMETER varchar(100), @SORTORDER varchar(50), @STARTROW int, @ENDROW int'

    IF @FOLDERID is null
    SET @sqlCommand = '
    SELECT AIWR.ID,
            AIWR.NAME,
            AIWR.TYPE,
            AIWR.STATUS,
            AIWR.TOPIC,
            AIWR.FolderName,
            AIWR.ISDELETABLE,
            AIWR.OWNERID
    FROM
    (select 
        ROW_NUMBER() OVER (ORDER BY ' + @SORTPARAMETER + ' ' + @SORTORDER + ') as ROW,
        ACTION_ITEM.[ID],
        ACTION_ITEM.[NAME],
        ACTION_ITEM.[TYPE],
        ACTION_ITEM.[STATUS],
        ACTION_ITEM.[TOPIC],

        (CASE WHEN ACTIONITEMFOLDER.FOLDERID = 0 THEN ''Action Item Gallery'' ELSE S.FolderName END) AS FolderName,
        1 AS ISDELETABLE,
        ACTION_ITEM.OWNERID
    from 
        dbo.ACTION_ITEM
        LEFT OUTER JOIN dbo.ACTIONITEMFOLDER
        ON ACTION_ITEM.ID = ACTIONITEMFOLDER.ACTIONITEMID
        LEFT OUTER JOIN dbo.SiteFolders S
        ON S.FolderID = ACTIONITEMFOLDER.FolderID
        AND S.Type = 6
        WHERE ACTION_ITEM.ID = ISNULL(@ACTIONITEMID,ACTION_ITEM.ID)) AS AIWR
    WHERE ROW >= @STARTROW AND ROW <= @ENDROW'
    ELSE
    SET @sqlCommand = '
    SELECT AIWR.ID,
            AIWR.NAME,
            AIWR.TYPE,
            AIWR.STATUS,
            AIWR.TOPIC,
            AIWR.FolderName,
            AIWR.ISDELETABLE,
            AIWR.OWNERID
    FROM
    (select 
        ROW_NUMBER() OVER (ORDER BY ' + @SORTPARAMETER + ' ' + @SORTORDER + ') as ROW,
        ACTION_ITEM.[ID],
        ACTION_ITEM.[NAME],
        ACTION_ITEM.[TYPE],
        ACTION_ITEM.[STATUS],
        ACTION_ITEM.[TOPIC],

        (CASE WHEN ACTIONITEMFOLDER.FOLDERID = 0 THEN ''Action Item Gallery'' ELSE S.FolderName END) AS FolderName,
        1 AS ISDELETABLE,
        ACTION_ITEM.OWNERID
    from 
        dbo.ACTION_ITEM
        INNER JOIN dbo.ACTIONITEMFOLDER
        ON ACTION_ITEM.ID = ACTIONITEMFOLDER.ACTIONITEMID
        AND ACTIONITEMFOLDER.FOLDERID = @FOLDERID
        LEFT OUTER JOIN dbo.SiteFolders S
        ON S.FolderID = ACTIONITEMFOLDER.FolderID
        AND S.Type = 6
        WHERE ACTION_ITEM.ID = ISNULL(@ACTIONITEMID,ACTION_ITEM.ID)) AS AIWR
    WHERE ROW >= @STARTROW AND ROW <= @ENDROW'

    exec sp_executesql @sqlCommand, @ParmDefinition, @ACTIONITEMID = @ACTIONITEMID, @FOLDERID = @FOLDERID, 
                                        @SORTPARAMETER = @SORTPARAMETER, @SORTORDER = @SORTORDER, @STARTROW = @STARTROW, @ENDROW = @ENDROW