That proc is not failing.
Try running it in ssms:
declare @p1 varchar(50) = 'test'
,@p2 int;
exec dbo.error_test @p1,@p2 output;
select @@ERROR ;
select @p2;
Now change the proc to actually fail:
alter procedure error_test (@p1 varchar(50), @p2 int output)
as
set @p2=45;
raiserror ('An error was raised. Input was "%s".', 9, 1, @p1);
set @p2 = @p1; -- causes conversion error if @p1 is non numeric
return 55;
GO
This will cause a conversion error if you run it as above.
Raiserror() doesn't do what you were expecting.
On Wed, Sep 8, 2010 at 4:24 PM, Roode, Eric <ero...@barrack.com> wrote:
> Hello all,
>
> I am trying to implement error handling in our stored procedures,
> but when I raise exceptions in the SQL code, they seem to disappear on
> their way back to the Perl app. I've searched the archives, but
> haven't found an answer.
>
> I'm using DBI 1.609 with DBD::ODBC 1.23 under strawberry Perl 5.12
> on Windows 7, against a SQL Server 2000 database elsewhere on our LAN.
>
> I've created a simple sproc to generate an error, as follows:
>
> create procedure error_test (@p1 varchar(50), @p2 int output)
> as
> set @p2=45;
> raiserror ('An error was raised. Input was "%s".', 16, 1, @p1)
> return 55
> GO
>
> Here is a simple Perl program to invoke it:
>
> use Modern::Perl;
> use DBI;
>
> sub handle_error
> {
> my ($state, $msg, $native) = @_;
> say qq{handle_error: \$state is "$state".};
> say qq{handle_error: \$msg is "$msg".};
> say qq{handle_error: \$native is "$native".};
> return 1;
> }
>
> my $dbh = DBI->connect('DBI:ODBC:driver={SQL
> Server};Server=xxxxx;Database=xxxxx,
> 'xxxxx', 'xxxxx',
> {odbc_cursortype => 2,
> odbc_default_bind_type => DBI::SQL_VARCHAR,
> odbc_err_handler => \&handle_error,
> RaiseError => 1,
> PrintError => 0,
> });
>
> my $sth = $dbh->prepare('{? = call error_test(?, ?)}');
>
> # Set up parameters
> my $retval;
> my $p1 = 'Some input parameter';
> my $p2 = 75;
> $sth->bind_param_inout(1, \$retval, 4000, DBI::SQL_VARCHAR);
> $sth->bind_param (2, $p1, DBI::SQL_VARCHAR);
> $sth->bind_param_inout(3, \$p2, 32, DBI::SQL_INTEGER);
>
> # Execute
> $sth->execute;
>
> # Show what we got
> $_ = defined($_)? qq{"$_"} : '{NULL}' for ($retval, $p1, $p2);
> say qq{After execute: \$retval is $retval.};
> say qq{After execute: \$p1 is $p1.};
> say qq{After execute: \$p2 is $p2.};
> say 'Done.';
>
>
> Sadly, handle_error never gets called. The output of the program is:
>
> After execute: $retval is {NULL}.
> After execute: $p1 is "Some input parameter".
> After execute: $p2 is "75".
> Done.
>
> On the other hand, if I do something like try to call a nonexistent
> stored procedure, handle_error does get invoked, and the output looks
> like:
> handle_error: $state is "42000".
> handle_error: $msg is "[Microsoft][ODBC SQL Server Driver][SQL
> Server]Could not find stored procedure 'blefh_error_test'.".
> handle_error: $native is "2812".
>
>
> What do I have to do to detect the SQL exception?
>
> Thanks,
> -- Eric
>
>
>
--
"Champions do not become champions when they win the event, but in the
hours, weeks, months and years they spend preparing for it. The
victorious performance itself is merely the demonstration of their
championship character." -T. Alan Armstrong
"The Ow that can be expressed is not the true Ow." - Ao Tzu
