Re: Correct Perl Syntax for Last Record Insert in MS SQL?

[Perobl]

SCOPE_IDENTITY() can be used this way to obtain the last record insert in MS SQL Server:

my $dbh = DBI -> connect("$dsn; server=$host; Database=$database", $us
+er, $auth, \%attr) || die "database connection not made: $DBI::errstr
+";

my $sth = $dbh -> prepare("INSERT INTO ocr_main (project_number, lead_
+order, employee_id, due_date, ocr_type, line_item, mech_rnr, mech_rfd
+, mech_rfa, mech_dre, ctrl_rnr, ctrl_rfd, ctrl_rfa, ctrl_dre) VALUES 
+(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); SELECT SCOPE_IDENTITY()")
+ || die "unable to prepare query";

$sth -> execute($project_number, $lead_order, $employee_id, $due_date,
+ $ocr_type, $line_item, $mech_rnr, $mech_rfd, $mech_rfa, $mech_dre, $
+ctrl_rnr, $ctrl_rfd, $ctrl_rfa, $ctrl_dre);

my $ocr_number = $sth -> fetchrow_array();

$sth -> finish();
$dbh -> disconnect();

Note I've added a SELECT along with the SCOPE_IDENTITY() function to the same INSERT string. Now simply use fetchrow_array() to retrieve the last record.

Maybe this will help someone else.