ephemeralz has asked for the wisdom of the Perl Monks concerning the following question:

how can i call stored procedure from ms sql? below is my stored procedure:
CREATE procedure dbo.sp_updateStorageSystem @type varchar(20), @model varchar(20), @serialnumber varchar(20), @codeversion varchar(20), @disks varchar(10), @IPAddress varchar(15), @setupID varchar(4) as Begin declare @exists int set @exists = 0 if exists (Select 1 From storageSystem Where serialnumber=@serial +number) begin set @exists = 1 update storageSystem set type=@type, model=@model, codeversion=@codeversion, disks=@di +sks where serialnumber=@serialnumber return @exists end else begin insert into storageSystem (type, model, serialnumber, codeversion +, disks, IPAddress) values (@type, @model, @serialnumber, @codeve +rsion, @disks,@IPAddress) end End GO here's my perl code: eval { $sql = q{ BEGIN :rv := sp_updateStorageSystem(parameter1_in => ?, parameter2_in => ? ); END; }; $sth = $dbh->prepare( $sql ); }; if ($@) { warn "prepare stored procedure failed. \n"; } for($count=0; $count < scalar(@IP); $count++) { for($sCount=0; $sCount < $s_count; $sCount++) { eval { $sth->bind_param(1, $ssID[$sCount], SQL_VARCHAR); $sth->bind_param(2, $IP[$count], SQL_VARCHAR); $sth->execute(); #$dbh->commit(); }; if( $@ ) { warn "Database error: $@\n"; #$dbh->rollback(); #just die if rollback is failing } } }
this the error i get: Database error: Can't call method "bind_param" on an undefined value thanks for help!

Replies are listed 'Best First'.
Re: how to call stored procedure from ms sql
by friedo (Prior) on Apr 25, 2005 at 23:16 UTC
    Can't call method "bind_param" on an undefined value

    The important information here is that $sth is undefined when you try to call bind_param on it. That happens when prepare fails. If you check prepare for errors, you'll get some more useful information:

    $sth = $dbh->prepare( $sql ) or die $dbh->errstr;

    You can wrap that in an eval block if you want, too.

      is that the correct syntax to call the stored procedure and passing parameters? after doing some research, there seems to be so many ways to do this and i tried every ways and none work.

        A few questions for you:

        1. can you call the stored procedure from your database shell? (I have no idea what mssql stored procedures should look like, but it's the first thing to try, because if it won't work from your database directly, it's not going to work from a Perl script).
        2. what is the error message that was in the database's error string, that friedo suggested you take a look at? (There might be someone on here who can read minds, but it's sure not me)
        3. could you ennumerate those 'every ways' that you've tried, and what the error messages were from them?
        4. could you give more information about the script, such as which DBD you're using?