USP_DATALIST_ADVOCACY_CUSTOM

CREATE procedure dbo.USP_DATALIST_ADVOCACY_CUSTOM
(
        @CUSTOMID int = null,
        @SORTPARAMETER varchar(50) = 'LASTNAME', -- any field returned by result set

        @SORTORDER varchar(4) = 'ASC', -- 'ASC' , 'DESC'

    @Criteria  varchar(200) = null
) with execute as owner
as
    set nocount on;

    DECLARE @sqlCommand nvarchar(max)

    DECLARE @ParmDefinition nvarchar(1000)

  DECLARE @FormatCriteria  nvarchar(200)
  SET  @FormatCriteria =@Criteria 
  IF @Criteria is null  
  BEGIN
              SET @FormatCriteria = '1=1'
    END


    SET @ParmDefinition = '@CUSTOMID int, @SORTPARAMETER varchar(50), @SORTORDER varchar(4), @Criteria  varchar(50)'

    SELECT  @sqlCommand = '
    select 
        ADVOCACY_CUSTOM.[ID],
        ADVOCACY_CUSTOM.[CUSTOMID],
        ADVOCACY_CUSTOM.[FIRSTNAME],
        ADVOCACY_CUSTOM.[LASTNAME],
        ADVOCACY_CUSTOM.[MIDDLENAME],
        ADVOCACY_CUSTOM.[ORGANIZATION],
        ADVOCACY_CUSTOM.[ADDRESSBLOCK],
        ADVOCACY_CUSTOM.[CITY],
        ADVOCACY_CUSTOM.[STATEID],
        ADVOCACY_CUSTOM.[COUNTRYID],
        ADVOCACY_CUSTOM.[ZIP],
        ADVOCACY_CUSTOM.[PHONE],
        ADVOCACY_CUSTOM.[FAX],
        ADVOCACY_CUSTOM.[EMAIL],
        ADVOCACY_CUSTOM.[ADDRESSBLOCKOVERRIDE],
        ADVOCACY_CUSTOM.[TITLE],
        ADVOCACY_CUSTOM.[SALUATIONOVERRIDE],
           STATE.[DESCRIPTION] AS STATE,
           COUNTRY.[DESCRIPTION] AS COUNTRY,
       ADVOCACY_CUSTOM.OWNERID

    from 
        dbo.ADVOCACY_CUSTOM
        LEFT OUTER JOIN dbo.COUNTRY
        ON COUNTRY.ID = ADVOCACY_CUSTOM.[COUNTRYID]
        LEFT OUTER JOIN dbo.STATE
        ON STATE.ID = ADVOCACY_CUSTOM.[STATEID]
    where ADVOCACY_CUSTOM.CUSTOMID = ISNULL(@CUSTOMID,ADVOCACY_CUSTOM.CUSTOMID) AND ' + @FormatCriteria

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

    exec sp_executesql @sqlCommand, @ParmDefinition, @CUSTOMID = @CUSTOMID,    @SORTPARAMETER = @SORTPARAMETER, @SORTORDER = @SORTORDER, @Criteria = @Criteria