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

Suppose I have a stored procedure like this: 

CREATE PROCEDURE dbo.Foo (
  @Required Int,
  @Optional Int = 0
) AS ...

[download]
I know I can call the procedure via 
my $sth2 = $db->prepare('EXEC dbo.Foo ?, ?');
$sth2->execute(1,2);

[download]
or 
my $sth1 = $db->prepare('EXEC dbo.Foo ?');
$sth1->execute(1); # use the default for the second param

[download]
But is there any way to specify I want to use the default value even with the first prepared statement? So that I don't have to prepare the same procedure twice...

I can't call $sth2->execute(1) since that triggers an error (execute failed: called with 1 bind variables when 2 are needed), I can't use undef since that would translate to NULL, ...

Thanks, Jenda
XML sucks. Badly. SOAP on the other hand is the most powerfull vacuum pump ever invented.

Replies are listed 'Best First'.
Re: DBI->MS SQL, optional parameters to stored procedures
by pajout (Curate) on Nov 02, 2005 at 17:01 UTC
    Can you write something as 
    if @Optional is NULL then @Optional = 0;

    [download]
    into your stored procedure?
[reply]
[d/l]
Re: DBI->MS SQL, optional parameters to stored procedures
by pajout (Curate) on Nov 02, 2005 at 17:14 UTC
    Or, you can add the third argument saying if stored proc. have to treat NULL as NULL or change it to 0.
    Complicated, but with constant count of args. I am afraid that there is no solution in DBI...
[reply]

      Well ... this is for yet another DBIx module. Something that'd make calling MS SQL stored procedures (since that's all I want from my data access layer, thank you very much) very easy. I have finding the parameters of stored procedures and handling output parameters done, but I can't find a way to handle the optional parameters or even how to find which ones are optional :-(

      The code currently goes somewhat like this: 

      package MyDB;
use DBIx::LazyMethod::MSSQL;
use vars qw(@ISA);
@ISA = qw(DBIx::LazyMethod::MSSQL);

sub new {
    my $class = shift;

    my $db = $class->SUPER::new(
        data_source  => 'dbi:ODBC:jobodbc2',
        user => 'xxx',
        pass => 'xxx',
        attr => { PrintError => 0, RaiseError => 0, LongReadLen => 655
+36, 'AutoCommit' => 1 },
        methods => {
            FetchUserInfo => {
                def => { adminid => 1 },
                ret => '%',
            },
            GetServerMailAddress => '$',
            GetFieldTitle => {
                def => {siteid => undef},
                ret => '$'
            }
        },
    );

    return $db;
}

1;

      [download]
      and then 
      use MyDB;
my $db = MyDB->new();

print Dumper($db->FetchUserInfo(userid => '577'));

my $email;
$db->GetServerMailAddress(email => $email);
print "\$email = $email\n";

my $title;
$db->GetFieldTitle( fieldtype => 'I', fieldid => 11, fieldtitle => $ti
+tle);
print "\$title = $title\n";

      [download]

      If I could at least find out what parameters are optional and what are the default values I could just call the procedures with all parameters and pass those defaults, but I can't find a way to do that apart from parsing the SQL :-(

      Jenda
      XML sucks. Badly. SOAP on the other hand is the most powerfull vacuum pump ever invented.

[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom