Monday, March 12, 2012

Paging problems

I am using a stored procedure to page some objects
The procedure looks like this:

CREATE PROCEDURE sw20aut.sw20_Kon
( @.sid_nr INT, @.sid_stl INT = 35, @.kid int )
AS BEGIN
SET NOCOUNT ON
DECLARE
@.rader INT, @.sid_antal INT, @.ubound int, @.lbound int

SELECT
@.rader = COUNT(*),
@.sid_antal = COUNT(*) / @.sid_stl
FROM
sw20aut.sw_kontakter WITH (NOLOCK)
WHERE
kund_id = @.kid AND del = '0'

IF @.rader % @.sid_stl != 0 SET @.sid_antal = @.sid_antal + 1
IF @.sid_nr < 1 SET @.sid_nr = 1
IF @.sid_nr > @.sid_antal SET @.sid_nr = @.sid_antal

SET @.ubound = @.sid_stl * @.sid_nr
IF(@.sid_antal > 0) SET @.lbound = @.ubound - (@.sid_stl - 1)
ELSE SET @.lbound = 0

SELECT
CurrentPage = @.sid_nr,
TotalPages = @.sid_antal,
TotalRows = @.rader

DECLARE @.ename VARCHAR(64), @.fname VARCHAR(64), @.konid VARCHAR(64)
SET ROWCOUNT @.lbound
SELECT @.ename = enamn, @.fname = fnamn, @.konid = kon_id FROM sw20aut.sw_kontakter WITH (NOLOCK)
WHERE kund_id = @.kid AND del = '0'
ORDER BY enamn, fnamn, kon_id
SET ROWCOUNT @.sid_stl
SELECT kon_id, enamn, fnamn FROM sw20aut.sw_kontakter WITH (NOLOCK)
WHERE enamn + fnamn + '~' + CAST(kon_id as VARCHAR(64)) >= @.ename + @.fname + '~' + @.konid AND (kund_id = @.kid AND del = '0')
ORDER BY enamn, fnamn, kon_id
SELECT startid = @.konid
SET ROWCOUNT 0
END

The big problem is that i need to display objet with the same name. In my book the best identifier is the PK and therefor i have sorted as above by ordering after LastName, FirstName, ContactId

After som thinking ive reached the conclusion that this dont work if the idnumbers isnt of the same length. as long as they are(for example two people named John Smith, one with id = '23' and one with id = '87' it works. If there ids would have been '23' and '1203' it will not work correctly) of the same length it works fine.

What im wondering is if anyone have a good solution to this? Only thing i can think of is filling all idnumbers with zeros to equal length. Dont know how and if this will affect performance though. Anyone has a practical solution to this?

Sorry I'm not clear what you want to do in the stored procedure, it may help to understand your issue if you can post some sample data and expecting result returned by the stored procedure. For paging result sets, if you're using SQL2005/Express you can try the new?Ranking functions

No comments:

Post a Comment