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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

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