USP_SEARCHLIST_APEXFORM

CREATE procedure dbo.USP_SEARCHLIST_APEXFORM 
(
     @NAME nvarchar(512) = null,
     @FORMTYPEID uniqueidentifier = null,
   @ISDEFAULT bit = 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,
   @SITEID int = null,
   @FAFFORMS varchar(50) = null
) with execute as owner
as
    set nocount on;
    set @NAME = '%' + COALESCE(@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

    SET @ParmDefinition = '@NAME nvarchar(512), @FORMTYPEID uniqueidentifier, @ISDEFAULT bit, @SORTPARAMETER varchar(100), @SORTORDER varchar(50), @STARTROW int, @ENDROW int, @SITEID int, @FAFFORMS varchar(50)'

    IF (@SORTPARAMETER='' OR @SORTPARAMETER is null)
    SET @SORTPARAMETER='NAME'

    SET @sqlCommand = '
    SELECT AF.ID,
           AF.NAME,
           ft.FORMTYPE,
           AF.DATECHANGED,
           AF.OWNERID,
       AF.SITEID,
           CU.USERNAME
    FROM
    (SELECT ROW_NUMBER() OVER (ORDER BY '


  Select @SORTPARAMETER =
    CASE
        WHEN @SORTPARAMETER = 'FORMTYPE' THEN 'FORMTYPEID '
        WHEN @SORTPARAMETER = 'DATECHANGED' THEN 'DATECHANGED '
        ELSE 'NAME '
  END

  SET @sqlCommand =@sqlCommand +@SORTPARAMETER

  Select @SORTORDER =
    CASE
        WHEN @SORTORDER = 'DESC' THEN 'DESC '
        ELSE 'ASC '
  END
  SET @sqlCommand =@sqlCommand +@SORTORDER

  SET @sqlCommand =@sqlCommand + ') AS ROW,
        APEXFORM.[ID],
        APEXFORM.[NAME],
        APEXFORM.[FORMTYPEID],
        APEXFORM.[DATECHANGED],
        APEXFORM.[OWNERID],
    APEXFORM.[SITEID]
    FROM 
        dbo.APEXFORM'

      set @sqlCommand=@sqlCommand + ' WHERE (NAME LIKE @NAME)' --To prevent SQLi, this should be parameterized. 


    if @FORMTYPEID is not null
        SET @sqlCommand =@sqlCommand +' AND (FORMTYPEID = @FORMTYPEID)'

    if @FAFFORMS is not null
        SET @sqlCommand =@sqlCommand +' AND (FORMTYPEID IN (Select ID FROM APEXFORMTYPE WHERE PRODUCTID=''A919502C-A2F6-4A56-9183-28E3F667916E''))'

    if @ISDEFAULT is not null
      SET @sqlCommand=@sqlCommand + ' AND (ISDEFAULT = @ISDEFAULT)'

    if @SITEID is not null
      SET @sqlCommand=@sqlCommand + ' AND (SITEID = @SITEID)'

      SET @sqlCommand =@sqlCommand + ') AS AF
    left outer join dbo.APEXFORMTYPE ft on ft.ID = AF.[FORMTYPEID]

    LEFT OUTER JOIN dbo.CLIENTUSERS CU ON CU.ID = AF.[OWNERID]'

    SET @sqlCommand =@sqlCommand + ' WHERE ROW >= @STARTROW AND ROW <= @ENDROW'

    if @SORTPARAMETER = 'FORMTYPEID'
       SET @sqlCommand=@sqlCommand + ' ORDER BY ft.FORMTYPE' 

    exec sp_executesql @sqlCommand, @ParmDefinition, @NAME = @NAME, @FORMTYPEID = @FORMTYPEID, @ISDEFAULT = @ISDEFAULT, 
                                        @SORTPARAMETER = @SORTPARAMETER, @SORTORDER = @SORTORDER, @STARTROW = @STARTROW, @ENDROW = @ENDROW, @SITEID=@SITEID, @FAFFORMS = @FAFFORMS