I tried debugging , and I used SQL Profiler too. The sql is correct. When I copy and paste it into the SQL Server it runs perfectly fine. This is the perl code:
my $sql =  <<END_ROW2;
    declare \@limit int 
declare \@offset int 
set \@offset = $offset; 
set \@limit = $limit; 
declare \@idxini int 
declare \@idxfim int 
select \@idxfim = \@offset * \@limit 
select \@idxini = \@idxfim - (\@limit-1); 
WITH paging AS 
( 
  SELECT 
  ROW_NUMBER() OVER (order by column_ID) AS rowid
  FROM Table1 N
 RIGHT OUTER JOIN Table2 NE ON NE.Event_Type = N.Event_Type
) 
select * 
from paging 
where rowid between \@idxini and \@idxfim 
order by rowid; ;
END_ROW2



     my $sth = $a_dbh->prepare($sql);
    my $rc = $sth->execute();
    if (!$rc) {
    &pl_fatalSQLError($sql,$DBI::errstr);
    }
    my $rowArrayref=$sth->fetchall_arrayref({});
 # Showing page number with link

    for(my $i=1;$i<$pagenum;$i++) 
    { 
    print "<a href='page.pl?PageType=$type&page=$i'>$i</a> "; 
    } 
 
    print "<br>"; 
    # Display result page wise
    
    foreach my $row(@$rowArrayref) {
       
print "<br>".$$row{'column_ID'};
    }
     print "</p>";

[download]

In reply to Re^2: pagination script to work with SQL server by Anonymous Monk
in thread pagination script to work with SQL server by pearlgirl

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.