I'm having issues retrieving the output parameters from a stored procedure. My work around for now was to just end the stored procedure with a select like the following:
SELECT @out1 AS 'out1', @out2 as 'out2', @out3 as 'out3';
That works fine, but I don't understand why I wasn't able to get the output parameters. Take the procedure below for example (I simplified it for posting purposes).
CREATE PROCEDURE [dbo].[test_procedure]
@out1 int OUTPUT,
@out2 int OUTPUT,
@out3 int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
...
business logic that sets those 3 variables
...
SET NOCOUNT OFF;
END
The official examples I find do not use references while others do. Not sure what is correct in this case and there is confusion (definitely on my part) on which is correct.
$sql = '{call dbo.test_procedure(?, ?, ?)}';
$out1 = 0;
$out2 = 0;
$out3 = 0;
$stmt = sqlsrv_query($this->db->conn_id,$sql,array(
array(&$out1, SQLSRV_PARAM_OUT),
array(&$out2, SQLSRV_PARAM_OUT),
array(&$out3, SQLSRV_PARAM_OUT),
));
Those three variables will remain 0. I've tried without reference, specified SQLSRV_PHPTYPE_INT and SQLSRV_SQLTYPE_INT, to no avail.
Any thoughts on what I am missing? I'm using the sqlsrv drivers version 3.0.1, SQL Server 2008 R2, and PHP 5.4.3.