Paging with dynamic SQL in Stored Procedure

Question

Post reply

Paging with dynamic SQL in Stored Procedure

Rino-426559

SSC Enthusiast

Points: 195
More actions
August 28, 2007 at 3:03 am

#125438

I am taking my first steps into stored procedures and I am working on a solution for efficiently paging large resultsets with SQL Server 2000 based on the example on 4Guys: http://www.4guysfromrolla.com/webtech/042606-1.shtml.
The problem with the stored procedure is that I am not able to get the correct row from where to start paging. I can get it to work in static SQL though. The problem seems to lie within the @First_Id variable.
I just can't seem to figure out how to solve this one. Does anyone have any thoughts on how to get it working correctly? Any help is greatly appreciated!
Below is the sproc:
ALTER PROCEDURE
dbo.spSearchNieuws
(
@SearchQuery NVARCHAR(100) = NULL,
@CategorieId INT = NULL,
@StartRowIndex INT,
@MaximumRows INT,
@Debug BIT = 0
)
AS
SET NOCOUNT ON
DECLARE
@Sql_sri NVARCHAR(4000),
              @Sql_mr NVARCHAR(4000),
              @Paramlist NVARCHAR(4000),
              @First_Id INT,
              @StartRow INT
-- StartRowIndex
SET ROWCOUNT @StartRowIndex
SELECT @Sql_sri = '
                           SELECT @First_Id = dbo.tblNieuws.NieuwsId FROM dbo.tblNieuws
                           WHERE 1 = 1'
IF @SearchQuery IS NOT NULL
            SELECT @Sql_sri = @Sql_sri + ' AND FREETEXT(dbo.tblNieuws.Nieuwskop, @xSearchQuery)'
IF @CategorieId IS NOT NULL
            SELECT @Sql_sri = @Sql_sri + ' AND dbo.tblNieuws.CategorieId = @xCategorieId'
SELECT
@Sql_sri = @Sql_sri + ' ORDER BY dbo.tblNieuws.NieuwsId DESC'
-- MaximumRows
SET ROWCOUNT
@MaximumRows
SELECT @Sql_mr = '
                           SELECT dbo.tblNieuws.NieuwsId, dbo.tblNieuws.NieuwsKop, dbo.tblNieuws.NieuwsLink, dbo.tblNieuws.NieuwsOmschrijving, dbo.tblNieuws.NieuwsDatum,
                           dbo.tblNieuws.NieuwsTijd, dbo.tblNieuws.BronId, dbo.tblNieuws.CategorieId, dbo.tblBronnen.BronNaam, dbo.tblBronnen.BronLink, dbo.tblBronnen.BiBu,
                           dbo.tblBronnen.Video, dbo.tblCategorieen.Categorie
                           FROM dbo.tblNieuws INNER JOIN
                           dbo.tblBronnen ON dbo.tblNieuws.BronId = dbo.tblBronnen.BronId INNER JOIN
                           dbo.tblCategorieen ON dbo.tblNieuws.CategorieId = dbo.tblCategorieen.CategorieId AND
                           dbo.tblBronnen.CategorieId = dbo.tblCategorieen.CategorieId
                           WHERE 1 = 1'
--
IF @First_Id IS NOT NULL
              SELECT @Sql_mr = @Sql_mr + ' AND dbo.tblNieuws.NieuwsId <= @First_Id'
IF @SearchQuery IS NOT NULL
              SELECT @Sql_mr = @Sql_mr + ' AND FREETEXT(dbo.tblNieuws.Nieuwskop, @xSearchQuery)'
IF @CategorieId IS NOT NULL
              SELECT @Sql_mr = @Sql_mr + ' AND dbo.tblNieuws.CategorieId = @xCategorieId'
SELECT
@Sql_mr = @Sql_mr + ' ORDER BY dbo.tblNieuws.NieuwsId DESC'
--
IF
@Debug = 1
PRINT
@Sql_mr
PRINT @Sql_sri
SELECT
@Paramlist = '
                              @xSearchQuery NVARCHAR(100),
                              @xCategorieId INT,
                              @First_Id INT'
EXEC
sp_executesql @Sql_sri, @Paramlist,
                             @SearchQuery, @CategorieId, @First_Id
EXEC
sp_executesql @Sql_mr, @Paramlist,
                             @SearchQuery, @CategorieId, @First_Id

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

No sure here but I think it boils down to you need to output your value

EXEC sp_executesql @Sql_sri, @Paramlist,

@SearchQuery, @CategorieId, @First_Id OUTPUT

otherwise you only input and never get the return value to have @First_id set.

Rino-426559 SSC Enthusiast Points: 195 More actions · Answer 2

Thanks for the reply!

It still did not do the trick, though I have created a workaround by making part of the procedure static and porting some of the logic to the codebehind of my aspx pages. I don't see any performance penalties or whatsoever yet, so for now this will work just fine for the project I am working on. Thanks again for your answer anyway!