Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Paging by using MSSQL Server Store procedure

Posted on 2005-04-18
4
Medium Priority
?
320 Views
Last Modified: 2012-06-22
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
Comment
Question by:speedbeeqs
4 Comments
 
LVL 28

Accepted Solution

by:
rafrancisco earned 2000 total points
ID: 13809036
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
 
LVL 3

Expert Comment

by:Satyabodhi
ID: 13809159
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13810528
And all of this begs the question as to why you are using a Cursor for paging.  But perhaps I am missing something.
0
 

Author Comment

by:speedbeeqs
ID: 13811342
Dear rafrancisco, that's great! so cool!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question