How to pass dynamic filters into stored procedure with paging

Question

I have a stored procedure that gets a list of items, sorts and applies paging.
However I also need to add filtering. So what I want to do is pass in a string of filters like "27='Some Company';32='Auckland'", and split it into a temp table (see below split code)

Proposed Split code

 CREATE TABLE #Filters 
    (       
        ModelEntityId int not null, 
        ValueText nvarchar(max)
    ) 

    WHILE (@pos <> 0)
    BEGIN
        SET @NextFilter = substring(@Filters,1,@Pos - 1)
        SET @SubPos = charindex('=',@NextFilter)

        insert into #Filters (ModelEntityId, ValueText) 
                Values (substring(@NextFilter, 1, @SubPos-1),
                        substring(@NextFilter,@subPos+1, len(@NextFilter))) 

        SET @Filters = substring(@Filters,@pos+1,len(@Filters))
        SET @pos = charindex('~',@Filters)
    END

My Data is stored in a very generic way so one 'record' might look like this

ContainerModelEntityId DataContainerId ModelEntityId ValueText
4                      17              5             'sunshine company'
4                      17              6             '12999'
4                      17              7             '01/12/2010'
...
4                      18              5             'moonlight company...  

• ContainerModelEntityId is the container type (i.e. business, person, etc)
• DataContainerId is the 'row'
• ModelEntityId is the 'field'
• ValueText is the actual value

Currently the sp below has a SortFieldId that is passed into into it, and lets say that is a 5, I then do a join on my data table and sort where the ModelEntityId = 5. However now I also want to do a join on the values in my #filter table and only return results where the values match (I have placed a comment in the code below to show where I think the logic should go). But at this point I have blown my mind, as set logic usually gives me a headache. Any help appreciated.

Current Stored Procedure

   ALTER PROCEDURE [dbo].[GetSortedIndex]   
        @ContainerModelEntityId int,
        @ParentRecordId         int,
        @SortFieldId            int,
        @PageIndex              int,
        @PageSize               int,
        @Ascending              bit 
    AS
    BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.
        SET NOCOUNT ON;

        CREATE TABLE #SelectedRecords 
        (       
            ContainerModelEntityId int not null, 
            DataContainerId int not null,
            DataInstanceId int not null,
            ParentDataContainerId int null 
        ) 

        DECLARE @LowerBound int, @UpperBound int

        -- Pagination   
        select @LowerBound = ((@PageIndex) * @PageSize)+1
        select @UpperBound = (@PageIndex+1) * @PageSize+1

        IF @Ascending = 1 
        BEGIN
            INSERT INTO #SelectedRecords
            SELECT ContainerModelEntityId, 
                           DataContainerId, 
                           DataInstanceId, 
                           ParentDataContainerId 
                    FROM
            ( 
                select di.ModelEntityId as 'ContainerModelEntityId',
                          dc.DataContainerId,
                          di.DataInstanceId, 
                          dv.ModelEntityId, 
                          dc.ParentDataContainerId,
                  ROW_NUMBER() OVER (ORDER BY dv.ValueText) AS row 
                from datacontainer dc
                inner join dataInstance di 
                           on dc.DataContainerId = di.DataContainerId
                        //some funky join on #Filter table to go here
                        left outer join dataValue dv 
                          on di.DataInstanceId = dv.DataInstanceId 
                          and dv.ModelEntityId=@SortFieldId
                where ISNULL(dc.ParentDataContainerId,0) 
                               = ISNULL(@ParentRecordId,0) 
                          and di.IsCurrent = 1 
                          and di.ModelEntityId = @ContainerModelEntityId
            ) tbl
                WHERE tbl.row >= @LowerBound AND
              tbl.row < @UpperBound
    END
    ELSE
    BEGIN
        INSERT INTO #SelectedRecords
        SELECT ContainerModelEntityId, DataContainerId, 
               DataInstanceId, ParentDataContainerId 
        FROM
        ( 
            select di.ModelEntityId as 'ContainerModelEntityId', 
                dc.DataContainerId, di.DataInstanceId, 
                dv.ModelEntityId, dc.ParentDataContainerId, dv.ValueText,
            ROW_NUMBER() OVER (ORDER BY dv.ValueText DESC) AS row 
        from datacontainer dc
        inner join dataInstance di 
                on dc.DataContainerId = di.DataContainerId
            //some funky join on #Filter table to go here
        left outer join dataValue dv 
                on di.DataInstanceId = dv.DataInstanceId 
                   and dv.ModelEntityId=@SortFieldId 
        where ISNULL(dc.ParentDataContainerId,0) = ISNULL(@ParentRecordId,0) 
                  and di.IsCurrent = 1 
                  and di.ModelEntityId=@ContainerModelEntityId 
        ) tbl
        WHERE tbl.row >= @LowerBound AND
              tbl.row < @UpperBound 
    END

    DECLARE @Count int
    SELECT @Count = (SELECT COUNT(*) FROM DataContainer dc
    INNER JOIN DataInstance di ON di.DataContainerId = dc.DataContainerId
    WHERE di.ModelEntityId = @ContainerModelEntityId 
     AND ISNULL(dc.ParentDataContainerId,0) = ISNULL(@ParentRecordId,0) 
     AND di.IsCurrent=1)

    SELECT ContainerModelEntityId, DataContainerId, 
           ParentDataContainerId, 
           isnull(dv.ModelEntityId, @sortFieldId) as 'ModelEntityId', 
           dv.ValueText, 
           @Count [TotalRecords] 
    FROM #SelectedRecords sr  
    left outer join dataValue dv ON sr.DataInstanceId = dv.DataInstanceId

END

Answer 1

OK, got something going now

ALTER PROCEDURE [dbo].[GetSortedIndex]  
@ContainerModelEntityId int,
@ParentRecordId         int,
@SortFieldId            int,
@PageIndex              int,
@PageSize               int,
@Ascending              bit = 1,
@Filters                varchar(max) = null 

AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON;

DECLARE @NextFilter NVARCHAR(100)
DECLARE @Pos INT
DECLARE @SubPos INT
DECLARE @NextPos INT

CREATE TABLE #Filters 
(       
    ModelEntityId int not null, 
    ValueText nvarchar(max)
) 

CREATE TABLE #SelectedRecords 
(   row int,    
    ContainerModelEntityId int not null, 
    DataContainerId int not null,
    DataInstanceId int not null,
    ParentDataContainerId int null 
) 

DECLARE @LowerBound int, @UpperBound int

-- Pagination   
select @LowerBound = ((@PageIndex) * @PageSize)+1
select @UpperBound = (@PageIndex+1) * @PageSize+1

IF (Len(@Filters)>0)
BEGIN
    SET @Filters = @Filters + '~'
    SET @Pos = charindex('~',@Filters)
    WHILE (@pos <> 0)
    BEGIN
        SET @NextFilter = substring(@Filters,1,@Pos - 1)
        SET @SubPos = charindex('=',@NextFilter)
        insert into #Filters (ModelEntityId, ValueText) Values (substring(@NextFilter, 1, @SubPos-1),substring(@NextFilter,@subPos+1, len(@NextFilter))) 
        SET @Filters = substring(@Filters,@pos+1,len(@Filters))
        SET @pos = charindex('~',@Filters)
    END     

    INSERT INTO #SelectedRecords    
        select  row,
               ContainerModelEntityId,
               DataContainerId, 
               DataInstanceId,
               ParentDataContainerId           
        from
        (
            select row_number() over (order by dv.valuetext) as row,
                   filtered.ContainerModelEntityId,
                   filtered.ParentDataContainerId, 
                   filtered.DataContainerId, 
                   filtered.DataInstanceId                    
            from dataValue dv
            join 
            (
                select dc.ModelEntityId as 'ContainerModelEntityId', 
                       di.DataInstanceId, 
                       di.DataContainerId,
                       dc.ParentDataContainerId 
                from datainstance di
                    join datavalue dv on di.Datainstanceid = dv.datainstanceid
                    join datacontainer dc on dc.DataContainerId = di.datacontainerId
                    join #filters f on dv.ModelEntityId = f.ModelEntityId and f.ValueText = dv.ValueText
                where ISNULL(dc.ParentDataContainerId,0) = ISNULL(null,0) and di.IsCurrent = 1 and di.ModelEntityId = @ContainerModelEntityId
                group by dc.ModelEntityId, dc.ParentDataContainerId, di.DataInstanceId, di.DataContainerId
                having (count(di.DataInstanceId) = (select count(ModelEntityId) from #Filters) ) 
            )
            filtered on filtered.dataInstanceId = dv.dataInstanceId
            where dv.ModelEntityId = @SortFieldId
        ) tbl
        WHERE row >= @LowerBound AND 
              row < @UpperBound 
END
ELSE
BEGIN
    INSERT INTO #SelectedRecords
    SELECT Row, ContainerModelEntityId, DataContainerId, DataInstanceId, ParentDataContainerId 
    FROM
    ( 
        select ROW_NUMBER() OVER (ORDER BY dv.ValueText) AS row,
               di.ModelEntityId as 'ContainerModelEntityId', 
               dc.DataContainerId, 
               di.DataInstanceId, 
               dv.ModelEntityId, 
               dc.ParentDataContainerId          
        from datacontainer dc
            inner join dataInstance di on dc.DataContainerId = di.DataContainerId
            left outer join dataValue dv on di.DataInstanceId = dv.DataInstanceId and dv.ModelEntityId=@SortFieldId
        where (@ParentRecordId is NULL or ISNULL(dc.ParentDataContainerId,0) = ISNULL(@ParentRecordId,0)) and 
               di.IsCurrent = 1 and di.ModelEntityId=@ContainerModelEntityId
    ) tbl
    WHERE tbl.row >= @LowerBound AND
          tbl.row < @UpperBound
END 


DECLARE @Count int
SELECT @Count = (SELECT COUNT(*) FROM DataContainer dc
INNER JOIN DataInstance di ON di.DataContainerId = dc.DataContainerId
WHERE di.ModelEntityId=@ContainerModelEntityId AND ISNULL(dc.ParentDataContainerId,0) = ISNULL(@ParentRecordId,0) AND di.IsCurrent=1)

    SELECT ContainerModelEntityId, DataContainerId, ParentDataContainerId, isnull(dv.ModelEntityId, @sortFieldId) as 'ModelEntityId', dv.ValueText, 
        @Count [TotalRecords] FROM
    #SelectedRecords sr  
    left outer join dataValue dv ON sr.DataInstanceId = dv.DataInstanceId
    ORDER BY
    CASE
        WHEN @Ascending = 1 THEN (RANK() OVER (ORDER BY sr.row ASC))
        WHEN @Ascending = 0 THEN (RANK() OVER (ORDER BY sr.row DESC))
    END

END