Paging via Sp med dynamisk input

Jeg bøvler lidt med paging i Store Procedure. Når alle poster skal med i en paging er der ingen problemer, men når det kun er bestemte som skal med, så volder det problemer.


Jeg har taget udgangspunkt (4guysfromrolla) http://www.aspfaqs.com/webtech/062899-1.shtml


Denne del tager alle poster med:
INSERT INTO #TempItems (*)
SELECT *  FROM  chain_members



Dette del tager alle poster med, når det tæller antal rækker, men viser ikke indhold af poster hvor chain_id er forskellig fra @chain_id
INSERT INTO #TempItems (*)
SELECT *  FROM  chain_members WHERE chain_id = @chain_id


Dette burde håndtere en dynamisk forespørgelse i SP

DECLARE @SearchSQL varchar(5000)

SELECT @SearchSQL = 'INSERT INTO #TempItems (*) ' +
            'SELECT * FROM chain_members WHERE chain_id ='+ @chain_id
EXECUTE(@SearchSQL)

men der kommer den fejl meddelelse:
Syntax error converting the varchar value 'INSERT INTO #TempItems (*) SELECT * FROM chain_members WHERE chain_id =' to a column of data type int.


Jeg vil gerne, at det kun er de poster, som opfylder kravet (WHERE) som vises og tælles med i en paging.


???:-) karsten_larsen




Hele min sp ser således ud

CREATE PROCEDURE chain_members_paging
    (
    @Page int,
    @RecsPerPage int,
    @Chain_id int
    )
AS

-- We don't want to return the # of rows inserted
-- into our temporary table, so turn NOCOUNT ON
SET NOCOUNT ON


--Create a temporary table
CREATE TABLE #TempItems
(
    chains_members_id     [int] NOT NULL primary key,
    chain_id     [int],
    chain_members_fornavn     [char](50),
    chain_members_efternavn     [char](100),
    chain_members_email     [char](100),
    chain_members_telephone     [int]
)


-- Insert the rows from tblItems into the temp. table
--
INSERT INTO #TempItems (*)
SELECT * FROM chain_members WHERE chain_id = @chain_id

-- Find out the first and last record we want
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1)

-- Now, return the set of paged records, plus, an indiciation of we
-- have more records or not!
SELECT *,
      MoreRecords =
    (
    SELECT COUNT(*)
    FROM #TempItems TI
    WHERE TI.chains_members_id >= @LastRec
    )
FROM #TempItems TI
WHERE TI.chains_members_id > @FirstRec AND TI.chains_members_id < @LastRec



-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GO