# SQL Server Pagination code here if you need it

**URL:** <https://www.ruby-forum.com/t/sql-server-pagination-code-here-if-you-need-it/225975>\
**Category:** Rails\
**Created:** [November 3, 2007, 8:17pm UTC](https://www.ruby-forum.com/t/sql-server-pagination-code-here-if-you-need-it/225975 "2007-11-03T20:17:23Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![surf](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/77aa72/32.png) [@surf](https://www.ruby-forum.com/u/surf)\
**Post date:** [November 3, 2007, 8:17pm UTC](https://www.ruby-forum.com/t/sql-server-pagination-code-here-if-you-need-it/225975/1 "2007-11-03T20:17:23Z")

</div>

# 

# Here is a SQL Server halfway decent working pagination I recently

wrote. Out of the box Rails

# pagination was not working very well at all. I found little on the

internet and thought

# if I could give something back to the Rails community in some way,

that would be cool.

# It was hastily written and there’s at least one bug on the

pagination link for page 2.

# I also think that ODBC driver for SQL Server might be better than

ADO

# 

# I put the code in application.rb

class MyPagination

def links()  
start = @cur\_page - 2  
lend = @cur\_page + 2

start = 1 if (start \< 0)  
lend = @num\_pages if (lend \> @num\_pages)

range = start…lend  
list = range.to\_a

# needs to eval to ‘…’, use a do nothing method like cap() etc

dot = ‘"…".capitalize’  
list = [1,dot] + list if start != 1  
list += [dot,@num\_pages] if lend != @num\_pages

arr = []  
prev = 0  
list.each do |pag|  
pstr = pag.to\_s  
prfx = “”

if (pag.class != String) && (pag != @cur\_page)  
str = "link\_to " + “’” + pstr + “’”  
@url.each do |ky,val|  
str += “,:#{ky.to\_s} =\> ‘#{val}’”  
end  
str += “, :page =\> ‘#{pag}’”  
#puts “add #{str} to arr”  
arr \<\< str  
else  
arr \<\< pstr  
end  
end  
arr

end

def recs()  
@recs  
end

def initialize(parm)  
@parm = parm  
@pkey = parm[:pkey]  
@rec\_class = parm[:rec]  
@url = parm[:url]  
end

def pages()  
nil  
end

def page\_count  
@num\_pages  
end

def paginate (page\_sz, page\_wanted, order)  
page\_wanted = 1 if !page\_wanted

page = nil  
if @rec\_class  
if @parm[:conditions]  
puts “cond: #{@parm[:conditions]}”  
size = @rec\_class.count(:conditions =\> @parm[:conditions])  
else  
size = @rec\_class.count  
end  
@num\_pages = size / page\_sz  
@last\_page\_sz = size.remainder(page\_sz)  
if @last\_page\_sz \> 0  
@num\_pages += 1  
end  
puts “call get”  
get\_page(page\_sz, page\_wanted, order)  
end

end

private

def get\_page(page\_sz,page\_wanted, order)  
=begin  
SELECT TOP 50 \*  
FROM mytab  
WHERE (EndUser NOT IN  
(SELECT TOP 15000 enduser  
FROM mytab  
ORDER BY enduser))  
ORDER BY EndUser  
=end

page\_wanted = 1 if !page\_wanted  
@cur\_page = page\_wanted  
recs\_wanted = page\_sz  
recs\_wanted = @last\_page\_sz if page\_wanted == @num\_pages  
puts “page\_sz:#{page\_sz} last\_sz:#{@last\_page\_sz}  
page:#{page\_wanted} recs\_wanted:#{recs\_wanted}”  
block\_sz = ((page\_wanted - 1) \* page\_sz)  
#block\_sz += recs\_wanted if page\_sz != recs\_wanted

rec\_name = @rec\_class.table\_name  
order\_clause = “”  
order\_clause = “order by #{order}” if order  
cond2 = “”  
cond1 = “”  
if @parm[:conditions]  
cond2 = @parm[:conditions]  
cond1 = cond2  
cond1 += " and " if page\_wanted.to\_i \> 1  
cond2 = "where " + cond2  
end

inner\_sql = “”  
if page\_wanted.to\_i \> 1  
inner\_sql = " not in " +  
" (select top #{block\_sz} #{@pkey} " +  
" from #{rec\_name} " + cond2 + " " + order\_clause +  
") "  
end

sql =  
"select top #{recs\_wanted} \* " +  
" from #{rec\_name} "  
if cond1 != “”  
sql += "where #{cond1} "  
end  
if inner\_sql != “”  
sql += " (#{@pkey} " + inner\_sql + ") "  
end

sql += " " + order\_clause

puts sql  
@recs = @rec\_class.find\_by\_sql(sql)  
puts “got #{@recs.size} records”

end

end

###################################################################

# 

# Controller example:

def index  
page = params[:page]

=begin  
@pages, @rec = paginate(:MyRec,  
:order =\> “account DESC”,  
:per\_page =\> 50)  
=end

@pages = MyPagination.new(:rec =\> Account, :pkey =\>  
‘AccountID’ , :url =\> {:controller =\> ‘account’}, :conditions =\>  
“status = ‘corrected’”)  
page = params[:page].to\_i if params[:page]

@pages.paginate(50,page , “accountNum Desc”)  
@recs = @pages.recs()

end

####################################################33

# template example

\<% if @pages && @pages.page\_count \> 1 %\>  
  
  
PAGES  
\<% @pages.links.each do |lnk| %\>  
\<%= eval lnk %\>  
\<% end %\>  
  
  
\<% end %\>  
\<% if @recs %\>  
\<%= render(:partial =\> ‘account’) %\>