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]