USP_DATALIST_APEXFORM

CREATE procedure dbo.USP_DATALIST_APEXFORM 
(
     @NAME nvarchar(512) = null,
     @FORMTYPEID uniqueidentifier = null,
     @ISDEFAULT bit = null,
   @SORTPARAMETER varchar(100) = 'NAME', -- NAME or STATUS or TYPE

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

   @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


    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), @SITEID int, @FAFFORMS varchar(50)'

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

    SET @sqlCommand = '
    SELECT   AF.[ID],
        AF.[NAME],
        ft.[SPECDATAFORMINSTANCEID],
        AF.[LAYOUT],
        AF.[ISDEFAULT],
        ft.[MAPPINGFILEPATH],
        ft.[FORMTYPE],
        AF.[FORMTYPEID],
        AF.[DATEADDED],
        AF.[DATECHANGED],
        AF.[SITEID]
    FROM
    (select  
        APEXFORM.[ID],
        APEXFORM.[NAME],
        APEXFORM.[LAYOUT],
        APEXFORM.[ISDEFAULT],
        APEXFORM.[FORMTYPEID],
        APEXFORM.[DATEADDED],
        APEXFORM.[DATECHANGED],
        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]'

SET @sqlCommand=@sqlCommand + ' ORDER BY ' + @SORTPARAMETER + ' ' + @SORTORDER

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