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>";
