USP_SEARCHLIST_ACTIONITEM

                CREATE procedure dbo.USP_SEARCHLIST_ACTIONITEM
                (
                    @NAME nvarchar(250) = NULL,
                    @STATUSCODE tinyint = NULL,
                    @TOPICCODE tinyint = NULL,
                    @TYPECODE tinyint = NULL,
                    @FOLDERID int = null,
          @MAXROWS smallint = 500,
          @SORTPARAMETER varchar(100) = 'NAME', -- NAME or STATUS or TYPE

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

            @PAGENUMBER int = 1,
            @PAGESIZE int = 50,
          @SITECONTENTID int = -1
                ) with execute as owner
                as
                    IF @NAME IS NOT NULL 
                    SET @NAME = '%' + @NAME + '%' ;

          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

          if ISNULL(@SORTPARAMETER, '') = ''
          SET @SORTPARAMETER = 'Name'

          if ISNULL(@SORTORDER, '') = ''
          SET @SORTORDER = 'ASC'

          SET @ParmDefinition = '@NAME nvarchar(250), @STATUSCODE tinyint, @TOPICCODE tinyint, @TYPECODE tinyint, @FOLDERID int, @SORTPARAMETER varchar(100), @SORTORDER varchar(50), @STARTROW int, @ENDROW int, @SITECONTENTID int'

          set @sqlCommand = '
          select AIWR.ID,
                 AIWR.NAME,
                 AIWR.TYPE,
                 AIWR.STATUS,
                 AIWR.TOPIC,
                 1 AS ISDELETABLE,
                 AIWR.OWNERID,
                 AIWR.USERNAME
          from
                        (select
              ROW_NUMBER() OVER (ORDER BY ' + @SORTPARAMETER + ' ' + @SORTORDER

          set @sqlCommand = @sqlCommand + ') as ROW,
                            AI.ID,
                            AI.[NAME],
                            AI.[TYPE],
                            AI.[STATUS],
                            AI.[TOPIC],
                            1 AS ISDELETABLE,
                            AI.[OWNERID],
                            CU.USERNAME
                        from 
                            dbo.ACTION_ITEM AI (NOLOCK)
                            INNER JOIN dbo.ACTIONITEMFOLDER WITH (NOLOCK)
                            ON AI.ID = ACTIONITEMFOLDER.ACTIONITEMID                  
                            LEFT OUTER JOIN dbo.CLIENTUSERS CU ON CU.ID = AI.[OWNERID]
                        where
                             AI.NAME LIKE ISNULL(@NAME,AI.NAME)
                             AND AI.STATUSCODE = ISNULL(@STATUSCODE,AI.STATUSCODE)
                             AND AI.TOPICCODE = ISNULL(@TOPICCODE,AI.TOPICCODE)
                             AND ACTIONITEMFOLDER.FOLDERID = ISNULL(@FOLDERID, ACTIONITEMFOLDER.FOLDERID)
                             AND AI.TYPECODE = ISNULL(@TYPECODE,AI.TYPECODE)
               AND AI.ID NOT IN (select ACTIONITEMID from ACTIONCENTER where SITECONTENTID = ISNULL(@SITECONTENTID,-1))
               ) as AIWR
            where (ROW >= @STARTROW AND ROW <= @ENDROW)'


         exec sp_executesql @sqlCommand, @ParmDefinition, @NAME = @NAME, @STATUSCODE = @STATUSCODE, @TOPICCODE = @TOPICCODE,
                    @TYPECODE = @TYPECODE, @FOLDERID = @FOLDERID, @SORTPARAMETER = @SORTPARAMETER,
                                        @SORTORDER = @SORTORDER, @STARTROW = @STARTROW, @ENDROW = @ENDROW, @SITECONTENTID = @SITECONTENTID