SQL Server Stored Procedure Output Params in PHP

Question

I need help running a stored procedure from SQL Server in PHP. PHP is running on a Unix/Linux server. We cannot get OUTPUT variables to return in PHP. The following is the PHP code:

$conn = mssql_connect('server', 'user', 'pass');
    mssql_select_db('db', $conn);

    $procedure = mssql_init('usp_StoredProc', $conn);

    $tmpVar1 = 'value';
    $tmpVar2 = 'value2';

    $outVar1 = '';
    $outVar2 = '';

    mssql_bind($procedure, "@var1", $tmpVar1, SQLVARCHAR, false, false);
    mssql_bind($procedure, "@var2", $tmpVar2, SQLVARCHAR, false, false);

    mssql_bind($procedure, "@outVar1", $outVar1, SQLVARCHAR, true);
    mssql_bind($procedure, "@outVar2", $outVar2, SQLVARCHAR, true);

    mssql_execute($procedure,$conn);

    print($outVar1);
    print($outVar2);

The stored procedure looks like so :

    SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[usp_StoredProc]
(
    @var1 as varchar(36), 
    @var2 as varchar(10), 
    @outVar1 varchar(36) OUTPUT, 
    @outVar2 varchar(36) OUTPUT 
)
as
  select distinct 
    @outVar1 = row1, 
    @outVar2 = row2
  from table1
  where column1 = @var1
    and column2 = @var2

Can anyone tell me why $outVar1 and $outVar2 are not being populated? Thanks a lot for any help!

Answer 1

I doubt this is causing your problem, but why are you using DISTINCT?

That's just a codesmell - any time you see that, it means that there is a potential for returning duplicates that is being "handled" with DISTINCT, and why duplicates would be returned probably needs to be looked at.

Answer 2

Try specifying the specific lengths of the output fields

mssql_bind($procedure, "@outVar1", &$outVar1, SQLVARCHAR, true, false, 36);
mssql_bind($procedure, "@outVar2", &$outVar2, SQLVARCHAR, true, false, 36);

And see if that makes a difference.

Also note the explicit & to pass the output vars by reference, though I don't know if it's still required or not.

Answer 3

Not sure which version of PHP you are running, but i think in some of the older ones you needed to pass variables by reference to get the value to come out again:

So you'd need to put the & charcter before the variable when calling the function:

mssql_bind($procedure, "@outVar1", &$outVar1, SQLVARCHAR, true);
mssql_bind($procedure, "@outVar2", &$outVar2, SQLVARCHAR, true);

Also according to this link some versions had a issue with output paramters

Answer 4

According to this page on PHP bugs, you have to (emphasis mine):

call mssql_next_result() for each result set returned by the SP. This way you can handle multiple results.

When mssql_next_result() returns false you will have access to output parameters and return value.

Answer 5

Hm. A few comments

1) "mssql_execute($procedure,$conn);" is wrong in that the 2nd parameter is not the connection.

2) If you are getting "stored procedure execution failed" then I had to create a DB host in freetds.conf and reference that.

At that point, I don't get errors but I don't get the output params either. This is PHP 5.1 on RHEL5.

If I enable freeTDS logging, I see the data come back in the return packet. At this point, I don't know why it isn't working either (other than the SQL server support for PHP is a bit lacking!)

Answer 6

The second param of execute needs to be true, rather than conn. This should work:

$conn = mssql_connect('server', 'user', 'pass');
mssql_select_db('db', $conn);

$procedure = mssql_init('usp_StoredProc', $conn);

$tmpVar1 = 'value';
$tmpVar2 = 'value2';

$outVar1 = '';
$outVar2 = '';

mssql_bind($procedure, "@var1", $tmpVar1, SQLVARCHAR, false, false);
mssql_bind($procedure, "@var2", $tmpVar2, SQLVARCHAR, false, false);

mssql_bind($procedure, "@outVar1", $outVar1, SQLVARCHAR, true);
mssql_bind($procedure, "@outVar2", $outVar2, SQLVARCHAR, true);

mssql_execute($procedure,true);

print($outVar1);
print($outVar2);

Answer 7

Did you ever find a solution to this? I am having the same problems.

Presuming that you are using the FreeTDS driver to communicate with SQL Server there is a known issue with the way the driver works. It's highlighted in the FAQ

http://www.freetds.org/faq.html#ms.output.parameters

The API docs for what is suggested in the FAQ is here but I can't find a way to access this with PHP:

http://www.freetds.org/reference/a00276.html

I still can't get this hooked up and I am at the point where I am going to give up on output parameters all together.