executng proc in sql 2005 and sql 2008 givng not proper result

Posted by Sushant under Sql Server on 5/30/2013 | Points: 10 | Views : 785 | Status : [Member] | Replies : 3
Hi all
when i am executing following procedure in sql2005 its working proper
CREATE PROCEDURE [dbo].[sp_GetHotProjectImagesDetails]
@PageIndex INT = 1
,@PageSize INT = 10
,@RecordCount INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SELECT ROW_NUMBER() OVER
(
ORDER BY [DispOrder] ASC
)AS RowNumber
,[ImageId]
,[ProjectId]
,[ImageUrl]
,[Active],
[DispOrder]

INTO #Results
FROM [HotProjectImages]

SELECT @RecordCount = COUNT(*)
FROM #Results

SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

DROP TABLE #Results
END
exec sp_GetHotProjectImagesDetails @RecordCount=3
its showing 3 records
when i am executing same in sql server 2008 its not shoing any record

Note:-table on both cases same data also same

Sushat


Responses

Posted by: Pandians on: 5/31/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
1. How many records do you have in Table in both version 2005 and 2008 ?
If both record count are same then!

According to your paging login
SELECT * FROM #Results
WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1

It will return 1 to 10 records, If you not passing any values to the parameter (So, It uses default value)

In OUT variable @RecordCount, You are passing 3. But again you overriting the variable inside the procedure
SELECT @RecordCount = COUNT(*)
FROM #Results

Im not sure what you want to do ?

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sushant, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Pandians on: 6/3/2013 [Member] [MVP] Silver | Points: 25

Up
0
Down
Got your problem solve ? Or get back for further discussion !

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Sushant, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sushant on: 6/5/2013 [Member] Starter | Points: 25

Up
0
Down
Thanks sir for replying
My prob is Resolved there was no prob in procedure,i dont know why it was not giving correct output but when i droped and recreated procedure workng fine

In OUT variable @RecordCount, You are passing 3. But again you overriting the variable inside the procedure
SELECT @RecordCount = COUNT(*)
FROM #Results

and above i am passing 3 because only 3 data in database to check procedure i was passing but it supposed to get it from count

thans again sir for replyng
i am freshers so i need your reply in future
and i want to add u in facebook for further help my fb id is sushandas@gmail.com if u r dont have prob plz add me

thanks



Sushat

Sushant, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response