Jump to content

MS SQLSRV Driver and Output Parameters


JDTornado

Recommended Posts

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!

Link to comment
Share on other sites

  • 3 weeks later...

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.