MS SQLSRV Driver and Output Parameters

[JDTornado]

Hi Everyone,

 

I have just started using the SQLSRV driver from Microsoft so I could work with SQL Server 2008 a bit better, but I am having some problems with the output parameters. I know how to access them by passing a variable by reference if I use "call StoredProcedure(?,?)", but then I have to list out every single parameter in a stored proc, rather than just binding the ones I need like the MSSQL driver does. I have been able to run a stored proc using something like "exec StoredProcedure @i_param1=?". But if I try to include an output parameter by doin something like "exec StoredProcedure @i_param1=?, @o_result=? OUTPUT". I get no value passed back to my variable that was passed by reference. Is anyone aware of a way to do this, or am I literally gonna have to pass every single parameter for every stored proc. Thanks for your advice!

[jadamski]

What happens if you set the variable before that stored procedure and pass that variable by reference rather than value. It seems like you should be able to populate an array that way if you want to return a kind of set or list.

[Brian Swan]

I'm wondering if this topic (How to: Retrieve Output Parameters) in the sqlsrv documentation might help: http://msdn.microsoft.com/en-us/library/cc626303(SQL.90).aspx

 

-Brian

[JDTornado]

I've actually read all through the help documentation, but that only shows how to execute a stored proc using call, and I would like to use exec so I can use specific parameters instead of needing to have a placeholder for every single parameter.

[Brian Swan]

Although the recommended way to execute store procedures witht the sqlsrv driver is to use the "call" syntax, you can use the EXEC syntax. So if your stored procerdure declares a default value for a param, like for @test here:

 

CREATE PROCEDURE GetEmployeeSalesYTD
@SalesPerson nvarchar(50),
@test nvarchar(50) = null,
@SalesYTD money OUTPUT
AS
SELECT @SalesYTD = SalesYTD
FROM Sales.SalesPerson AS sp
JOIN HumanResources.vEmployee AS e 
ON e.BusinessEntityID = sp.BusinessEntityID
WHERE LastName = @SalesPerson

 

...then you shoul be able to do this...

 

$tsql_callSP = "EXEC GetEmployeeSalesYTD @SalesPerson=?, @SalesYTD=?";
$lastName = "Blythe";
$salesYTD = 0.0;
$params = array( 
                 array($lastName, SQLSRV_PARAM_IN),
                 array($salesYTD, SQLSRV_PARAM_OUT)
               );

/* Execute the query. */
$stmt3 = sqlsrv_query( $conn, $tsql_callSP, $params);
if( $stmt3 === false )
{
     echo "Error in executing statement 3.\n";
     die( print_r( sqlsrv_errors(), true));
}

/* Display the value of the output parameter $salesYTD. */
echo "YTD sales for ".$lastName." are ". $salesYTD. ".";

 

Does that help?

[JDTornado]

I tried that, but am not getting values from the output parameters. The only time that the parameter gets passed back is when I use call. Have you tested your method? If so, were you able to get the value of the ouput parameter using exec?

[Brian Swan]

Yes, I've tested it and it works. Can you share both the stored procedure definition and the PHP code that calls it? (Or some sproc/php code that reproduces the issue?) I'll look into it further.