sorry for inconvinience

Im using operating system: Windows XP

database driver: DBD::ODBC

i want give customer name as input to the procedure (Ex-procedure name) and it checks whether the name is already present in the table and insert the customer name if it is not in the table.

here is sample code

#!/usr/local/bin/perl
use DBI;
use DBD::ODBC;
my $dbh = DBI->connect("dbi:ODBC:dsnentry", '', '', {PrintError => 0})
+;
die "Unable for connect to server" unless $dbh;
$dbh->{odbc_SQL_ROWSET_SIZE} = 2;
$query="Exec ex 'habibrahman'";
my $result=$dbh->prepare($query) or print "cannot prepare" ;
$result->{LongReadLen}=512*1024;
$result->{LongTruncOk}=1;
$result->execute() or print $result->errstr();
[download]

and here is my procedure

create procedure ex (@name varchar(200))
as
declare @cust_id int
where cust_name=@name
if(@cust_id='')
begin
insert into x(cust_name) values(@name)
end
GO
[download]

Edited by planetscape - added code tags

The stored procedure isn't valid in ms sql 2000, because of this line:

where cust_name=@name
[download]

Perhaps you intended this?

select @cust_id = cust_id from x where cust_name=@name
[download]

I tested the perl and SQL you posted with the above modification and experienced the same problem when run from Windows XP using perl 5.8.8, dbd::odbc 1.13. I do not experience this problem on openbsd using perl 5.8.4, dbd::odbc 1.13, and iodbc.

Looking into it further the issue is this line:

$dbh->{odbc_SQL_ROWSET_SIZE} = 2;
[download]

With that line I get the error, without it I don't. Please review DBD::ODBC and check if this option is what you want.

By the way - when testing for the non-existence of a record in sql server you should check 'IS NULL', not compare to an empty string. Like this:

if(@cust_id IS NULL)
[download]