Re^2: calling sql server stored procedure

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

( keep:0 edit:11 reap:0 )

Comment on Re^2: calling sql server stored procedure Select or Download Code

Replies are listed 'Best First'.
Re^3: calling sql server stored procedure by imp (Priest) on Jul 26, 2006 at 12:28 UTC
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]	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^3: calling sql server stored procedure
by imp (Priest) on Jul 26, 2006 at 12:28 UTC

where cust_name=@name
[download]

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

Looking into it further the issue is this line:

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

DBD::ODBC

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]

[reply]
[d/l]
[select]