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!

Solution 1

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.

Solution 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.

Comments

[Geek 8]

Distinct could be left out, but we're looking for distinct combinations of row1 and row2. The stored procedure runs fine if we run it from the SQL Server Management tools. It's getting the output parameters in php is what we're having trouble with.

[Tomalak]

This would have been my next thought as well, +1. Providing a string of 36 spaces might also serve as a useful test.

[Geek 8]

Yeah, we've tried it all with/without lenghths. With the by-reference params(which throws and error now saying that way is deprecated) and without.

[Tomalak]

Regarding the explicit reference passing - don't think this is needed. The documentation states that the function takes references only.

[Joe]

I have this vague recollection that you used to have to explicitly pass by ref, though I see now that the docs say it's all by ref all the time. Thanks.

[Geek 8]

Yeah, we are running php 5.*, forgot the exact version, but those problems shouldn't persist in this version. The pass-by-reference method throws an error that says 'passing by reference in mssql_bind has been deprecated'

[Cade Roux]

OK. Keep in mind that on SELECT assignments like this, any time your resultset would contain more than a single row, you will get an error. Any time the result set is empty, the variables will be unaltered (NULL if they were never assigned).

[Cade Roux]

Tomalak is right about having to have consumed all the resultsets before you have access to the OUTPUT parameters. I had trouble with this when I wanted to use the OUTPUT parameters to return count estimates that the client could use prior to consuming the rows - not going to work.

[Geek 8]

Thanks, Joe. We had already removed the $conn param from mssql_query. Yeah, I just ran this script from my Windows box via WampServer and it worked fine. So, I believe you're right in that SQL Server support is a bit lacking in PHP on Unix/Linux.

[Joe]

You can pass an optional 2nd param to mssql_execute which (if true) gives you immediate access to the results without having to call mssql_next_result... It still doesn't help here, unfortunately.

[Tomalak]

Sounded plausible at first, though. :-\

[Geek 8]

I've seen this several places, but how would one do this? You have to execute a stored proc using mssql_execute, which does not return a resource.

[Cade Roux]

$result = mssql_execute($my_procedure); while(mssql_next_recordset($result)) { ## do something }

[icc97]

This will work if you don't know if there going to be results returned from the stored procedure, i.e. $result = mssql_execute($procedure), works whether there are results returned or not, you can then call mysql_next_result($result) to get at the output params