SP with paging and individual record select

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SP with paging and individual record select

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2012-07-19 : 23:39:41

Team,

I am trying to use the below SP for two different purpose,

1. Fetch all the records without passing @FileID, Used to bind all the records in Grid and used for the custom paging. ( This is working fine as per expected )

2. Pass @FileID to get the individual record, this is working fine if i give @fineId below 12 but not working if i pass the @fileId more than that value

Find my Sp below

-- SAMPLE DATA
/*
Declare @Total INT

EXEC SP_Files_Select
@FileID = 32,
@StatusID = null,
@StartIndex = 1,
@PageSize = 10,
@TotalCount = @Total OUTPUT

SELECT @Total as TotalCount

*/

ALTER PROCEDURE SP_Files_Select
(
@FileID INT = NULL,
@StatusID INT = NULL,
@StartIndex INT,
@PageSize INT,
@TotalCount INT = NULL OUTPUT
)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @EndIndex INT

IF @StartIndex <= 1
BEGIN
SET @StartIndex = 0
END
ELSE
BEGIN
SET @StartIndex = (@StartIndex - 1) * @PageSize
END

SET @EndIndex = @StartIndex + @PageSize

SELECT ROW_NUMBER() OVER (ORDER BY [FileID]) + @StartIndex AS SNO
,[FileID]
,[FileLocation]

FROM
(SELECT ROW_NUMBER() OVER (ORDER BY [FileID]) + @StartIndex AS Row,
,[FileID]
,[FileLocation]
FROM [Input_Files]) AS RecordRows
WHERE Row > @StartIndex AND
Row <= @EndIndex AND
(@FileID IS NULL OR FileID = @FileID) AND
(@StatusID IS NULL OR StatusID = @StatusID)

SELECT @TotalCount = COUNT (1)
FROM [Input_Files]
WHERE (@FileID IS NULL OR FileID = @FileID) AND
(@StatusID IS NULL OR StatusID = @StatusID)

END

It will be much appreciated if you help to get the expected result and fine tune.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-20 : 00:06:17

first check in nput_Files if you've records with FileID > 12

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dhinasql
Posting Yak Master

195 Posts

Posted - 2012-07-20 : 00:18:12

Thanks for your reply

SELECT * FROM [Input_Files] WHERE FileID > 12

Returns more than 100 records.

Something strange in my SP, It need to be fixed, please provide suggestion

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-20 : 00:31:26

ok i got it. you're doing the filtering only after paging. it should be other way around

see


ALTER PROCEDURE SP_Files_Select 
( 
@FileID INT = NULL, 
@StatusID INT = NULL, 
@StartIndex INT, 
@PageSize INT, 
@TotalCount INT = NULL OUTPUT 
) 
AS 
BEGIN 
SET NOCOUNT ON; 

DECLARE @EndIndex INT 

IF @StartIndex <= 1 
BEGIN 
SET @StartIndex = 0 
END 
ELSE 
BEGIN 
SET @StartIndex = (@StartIndex - 1) * @PageSize 
END 

SET @EndIndex = @StartIndex + @PageSize 

SELECT ROW_NUMBER() OVER (ORDER BY [FileID]) + @StartIndex AS SNO 
,[FileID] 
,[FileLocation] 

FROM 
(SELECT ROW_NUMBER() OVER (ORDER BY [FileID]) + @StartIndex AS Row, 
,[FileID] 
,[FileLocation] 
FROM [Input_Files]
WHERE (@FileID IS NULL OR FileID = @FileID) AND 
(@StatusID IS NULL OR StatusID = @StatusID)) AS RecordRows 
WHERE Row > @StartIndex AND 
Row <= @EndIndex 
 

SELECT @TotalCount = COUNT (1) 
FROM [Input_Files] 
WHERE (@FileID IS NULL OR FileID = @FileID) AND 
(@StatusID IS NULL OR StatusID = @StatusID) 

END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dhinasql
Posting Yak Master

195 Posts

Posted - 2012-07-20 : 00:51:17

Thanks visakh, its working great :-)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-20 : 00:55:11

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources