PHP calls the MsSQL Server 2012 stored procedure to get a detail of the multiple result set of containing the output parameters
- 2020-07-21 07:10:48
- OfStack
【PHP Source Code】 :
【SQL PROCEDURE】 :
[1 Some cautions] : When there are parameters in bindParam that need to output type, the length must be included ($length).
[Remarks] : $length: An optional (integer) length of the can PDO::SQLSRV_PARAM_OUT_DEFAULT_SIZE to the default size ES3en ::PARAM_INT or PDO: : PARAM_BOOL in $data_type.
$dbh = new PDO('sqlsrv:server= Connection address ;Database= The database name ', The user name , password );
try {
$procName = "P_Test_GetMixData";
$stmt = $dbh->prepare("EXEC $procName ?, ?, ?");
$nReturnValue = 0;
$strReturnValue = "";
$strSearchValue = "abandonship";
$stmt->bindParam(1, $nReturnValue, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT, PDO::SQLSRV_PARAM_OUT_DEFAULT_SIZE);
$stmt->bindParam(2, $strReturnValue, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT, 10);
$stmt->bindParam(3, $strSearchValue , PDO::PARAM_STR);
$stmt->execute();
// For the first 1 A result set .
$rowset_1 = $stmt->fetch(PDO::FETCH_ASSOC);
print_r($rowset_1);
echo '<br><br>';
// For the first 2 A result set .
$stmt->nextRowset();
$rowset_2 = $stmt->fetch();
print_r($rowset_2);
echo '<br><br>';
$stmt->nextRowset();
// Gets parameters of both output types
echo $nReturnValue.'<br><br>';
echo $strReturnValue;
} catch (Exception $e) {
echo $e->getMessage();
}
【SQL PROCEDURE】 :
/**
* Used for testing PDO call MsSQLServer2012 The stored procedure gets the composite result set Demo
* Code CreateBy abandonship 2012.10.11
**/
CREATE PROCEDURE [dbo].[P_Test_GetMixData](
@Message_1 tinyint output,
@Messgae_2 varchar(10) output,
@SearchValue varchar(50)
) As
set nocount on
set @Message_1 = 123
set @Messgae_2 = 'Hi,there!This is abandonship!'
select * from _T1 where col1 like '%'+@SearchValue+'%'
select * from _T2 where col1 like '%'+@SearchValue+'%'
set nocount off
[1 Some cautions] : When there are parameters in bindParam that need to output type, the length must be included ($length).
[Remarks] : $length: An optional (integer) length of the can PDO::SQLSRV_PARAM_OUT_DEFAULT_SIZE to the default size ES3en ::PARAM_INT or PDO: : PARAM_BOOL in $data_type.