Jump to content

Archived

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

JDTornado

MS SQLSRV Driver and Output Parameters

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!

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
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.

Share this post


Link to post
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?

Share this post


Link to post
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?

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

×
×
  • 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.