• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

Paging by using MSSQL Server Store procedure

Dear all experts, I have a store procedure which use to paging. I want to paging it follow @noOfRow but it raise error when I click "check syntax" button. Can I customise query in this store?

CREATE proc getSplitpager @curpage int,@noOfRow int
as
if @curpage < 0
      set @curpage=0
-- check num of page
declare @numrowperpage int
declare @condition int
declare @from int
-- test detail
declare @id int

DECLARE test_cursor SCROll CURSOR
--   FOR SELECT INEWS_ID FROM TestView where INEWS_BOARDCAST=1 order by INEWS_ID desc
   FOR SELECT * FROM TestView
OPEN test_cursor
-- check page:
-- if( @@CURSOR_ROWS % @numrowperpage > 0)
-- check numrows again
if(@curpage * @numrowperpage > @@CURSOR_ROWS)
      set  @curpage = @curpage - 1
set  @numrowperpage = @noOfRow
set @from = @numrowperpage * @curpage
if @from = 0
      set @from = 1
--while(@@FETCH_STATUS = 0)
--begin
      FETCH ABSOLUTE @from FROM test_cursor INTO @id
      set @condition = @id
--end
CLOSE test_cursor
DEALLOCATE test_cursor
--print @condition
select top @noOfRow * from TestView                                                  <-- ERROR RAISE HERE
GO
0
speedbeeqs
Asked:
speedbeeqs
1 Solution
 
rafranciscoCommented:
You cannot use a variable in a SELECT TOP statement.  An alternative is to execute a dynamic SQL as such:

Change this part:

--print @condition
select top @noOfRow * from TestView

to this:

DECLARE @SQL VARCHAR(200)
SET @SQL = 'select top ' + CAST(@noOfRow AS VARCHAR(10)) + ' * from TestView'
EXEC (@SQL)
0
 
SatyabodhiCommented:
Although you may not use a variable in a SELECT TOP n,
You can use:
SET ROWCOUNT @noOfRow
SELECT * from TestView
SET ROWCOUNT 0
0
 
Anthony PerkinsCommented:
And all of this begs the question as to why you are using a Cursor for paging.  But perhaps I am missing something.
0
 
speedbeeqsAuthor Commented:
Dear rafrancisco, that's great! so cool!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now