FreeTDS Development Group

["James K. Lowden" <jklowden AT schemamania.org>]

On Mon, 6 Jan 2003 17:51:07 -0700, "Chris McDaniel"
<Chris.McDaniel AT telus.com> wrote:
> I thought about that, but I get an error back saying I can't use the
> OUTPUT notation if I'm passing in a constant, but passing a null breaks
> DBI, so I'm a little confused...  I am not a T-SQL expert by any
> stretch, but I was guessing that since the variables are declared with
> the output statement in the stored procedure, maybe I didn't have to do
> it too.  

I guess after 10 years of experience with T-SQL, I'll claim to be an
expert.  :-)

This for sure: if you don't call a proc with "output" after the variable
name, the server won't write to it.  

You may not do:
        exec procname 'data' output
or
        exec procname @paramname = 'data' output

because there's nowhere for the server to write its output.  That would
yield the "passing a constant" error.

To provide a place to write the output, declare a variable:

        declare @out exec procname @paramname=@out output

Here's an example session:

[begin session]
1> create proc a @io int output as select @io=1
2> go
1> exec a 1     -- OK, passes constant as input
2> exec a @io=9 -- OK, passes constant as named input
3> go
(return status = 0)
(return status = 0)
1> exec a @io=9 output
2> go   -- not OK: server cannot write return value to constant '9'
Msg 179, Level 15, State 1, Server CPRO200, Line 1
Cannot use the OUTPUT option when passing a constant to a stored
procedure.
1> declare @o int select @o=9 exec a @io=@o output select @o
2> go   -- OK, server writes return value of @io to variable '@o'
(return status = 0)
        
1       
declare @o int select @o=9 exec a @o output select @o
go      -- OK, passes @o variable as positional output parameter 
(return status = 0)
        
1       
[end session]

That's what has to arrive at the server.  How you conjure that from
DBD::ODBC, I don't know because I don't use it.  :(  I'd be tempted to try
something like this:

$sth = $dbh->prepare('
        declare @DDI int, [...] @AvailableInd int
        select  @DDI = ? [...] @AvailableInd = ?
        execute p_ccs_MakeBooking [...] @AvailableInd=@AvailableInd OUTPUT'
$sth->bind_param_inout(8, \$AvailInd, 1, DBI::SQL_BIT);

or perhaps:

$sth = $dbh->prepare('
        declare @DDI int, [...] @AvailableInd int
        select  @DDI = 32 [...] @AvailableInd = 1
        execute p_ccs_MakeBooking [...] @AvailableInd=? OUTPUT'
$sth->bind_param_inout(8, \$AvailInd, '@AvailableInd', DBI::SQL_BIT);

which I'd hope to yield:
        
        execute p_ccs_MakeBooking [...] @AvailableInd=@AvailableInd OUTPUT

which would at least give the server something it recognizes.  

Hope that's of some help.

--jkl