FAQ: How do I retrieve a short length varchar/nvarchar OUTPUT parameter from a SQL Server stored procedure with SQL Server Driver for PHP?

Question

My stored procedure has a varchar(100)/nvarchar(100) output parameter, however it seems that I have to initialize a string variable with the specified length before I pass it into the output parameter. For example, $op_param=str_repeat("\0",100). This is not desirable.

Answer

You do not need to do this. Instead you can explicitly specify the output parameter's length when you bind the parameters with an array. Take the following stored procedure for example:

CREATE PROCEDURE [dbo].[getDocument1](@docId int, @docSummary nvarchar(100) out)

as

SELECT @docSummary = Substring(DocumentSummary,1,100) FROM Production.Document

WHERE DocumentID=@docID

GO

You can refer to the following code:

<?php

/*Connect to the local server using Windows Authentication and

specify the AdventureWorks database as the database in use. */

$serverName = "(local)";

$connectionInfo = array( "Database"=>"AdventureWorks");

$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn === false )

{

   echo "Could not connect.\n";

   die( print_r( sqlsrv_errors(), true));

}

/* Set up the Transact-SQL query. */

$tsql = "{call dbo.getDocument1( ? ,?)}";

/* Set the parameter value. */

$docId = 3;

$params = array(

                   array($docId,SQLSRV_PARAM_IN),

                   array($docSummary, SQLSRV_PARAM_OUT,SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR),SQLSRV_SQLTYPE_NVARCHAR(100))

                   );

/* Execute the query. */

$stmt = sqlsrv_query($conn, $tsql, $params);

if( $stmt === false )

{

   echo "Error in statement execution.\n";

   die( print_r( sqlsrv_errors(), true));

}

echo "OUTPUT: ".$docSummary;

/* Free the statement and connection resources. */

sqlsrv_free_stmt( $stmt);

sqlsrv_close( $conn);

?>

If your output parameter's type is varchar(max)  or the length is big (e.g. over 4000), you cannot directly get the output parameter with this method due to stream type data not able to be used as output parameters. For more information, you can refer to my another article, How do I retrieve varchar(max)/nvarchar(max) OUTPUT parameters from a SQL Server stored procedure with SQL Server Driver for PHP.