CODEWITHSUNDEEP
phpsqlsql-serverstored-proceduressqlsrv
Recnats
Recnats

Reputation: 377

SQLSRV Query Returning Output Parameter As Strange Text

I have a stored procedure in SQL Server 2012 which first checks if data exists, if it does then it sets the Output parameter @Return to 1 and runs a select query and if not, sets @Return to 0 and returns a different select query.

When testing this stored procedure to ensure the data is accurate it is perfect and returns the data I am expecting. The problem lies on the PHP side when trying to read the output parameter it is showing ��t_rrr when it should be showing a 1 or 0. I believe the problem may be in the Predefined Constant in the sqlsrv_query but i cannot seem to get it working. Here is my code:

PHP:

if(isset($_GET['accno'])) {

$search = $_GET['accno'];

$return = "";

$tsql_callSP = "EXEC Intranet.CustomerSearch @Search=?, @Return=?";
$params = array( 
    array($search, SQLSRV_PARAM_IN),
    array($return, SQLSRV_PARAM_OUT, SQLSRV_PHPTYPE_STRING(SQLSRV_ENC_CHAR),SQLSRV_SQLTYPE_INT),
    );

/* Execute the query. */
$stmt = sqlsrv_query( $conn, $tsql_callSP, $params);
if( $stmt === false )
{
    echo "EXEC Intranet.CustomerSearch Failed\n";
    die( print_r( sqlsrv_errors(), true));
}

while($res = sqlsrv_next_result($stmt))
{
    // make sure all result sets are stepped through, 
    // since the output params may not be set until this happens
}

echo $return;

if($return == 0) { ?>

   //1st Result Set
   while($row = sqlsrv_fetch_array($stmt)) {
   }

<?php } elseif($return == 1) { // End if Return 0 ?>

   //2nd Result Set
   while($row = sqlsrv_fetch_array($stmt)) {
   }

<?php } // End if Return 1 ?>

SQL Stored Procedure:

USE [Intranet]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [Intranet].[CustomerSearch]
    @Search nvarchar(10)
    ,@Return int output
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS
        (
            SELECT KeyCode
            FROM Autopart.dbo.Customer
            WHERE KeyCode = @Search
        )
    BEGIN

        SELECT
            Customer.KeyCode
            ,Customer.X13
            ,Customer.Name
            ,Customer.Addra
            ,Customer.Addrb
            ,Customer.Addrc
            ,Customer.Addrd
            ,Customer.Addre
            ,Customer.PCode
            ,Customer.CTitle
            ,Customer.Climit
            ,Customer.Ptype
            ,Customer.Stel
            ,Customer.SCont
            ,Customer.ACont
            ,Customer.XString5
            ,Customer.Locked
            ,Customer.Email
            ,Customer.StopStatus
            ,CusNotes.Comment
        FROM
            Autopart.dbo.Customer
            LEFT OUTER JOIN Autopart.dbo.CusNotes ON Autopart.dbo.Customer.KeyCode = Autopart.dbo.CusNotes.Account
        WHERE
            (Customer.KeyCode = @Search)
            AND (CusNotes.Seqno = 1 OR CusNotes.Seqno IS NULL)

        SET @Return = 1

    END ELSE BEGIN

        SELECT TOP 100
            KeyCode
            ,Name
            ,PCode
            ,Addra
            ,Addrb
            ,Addrc
        FROM
            AUTOPART.dbo.Customer
        WHERE
            (KeyCode LIKE '%'+@Search+'%'
            OR Name LIKE '%'+@Search+'%'
            OR PCode LIKE '%'+@Search+'%')

        SET @Return = 0

    END
END

I have tried changing the PHP and SQL types around but can't get the desired result. What is strange is if I create a stored procedure that is an INSERT or UPDATE statement the OUTPUT returns correctly.

EDIT:

SQL Server Stored Procedure Collation is Latin1_General_CI_AS

Upvotes: 1

Views: 5972

Answers (2)

Vladimir Baranov
Vladimir Baranov

Reputation: 32695

I happened to have SQL Server and PHP installed on my VirtualBox, so I played around.

Few things:

  • Init parameters with the value of a proper type.
  • Use & when passing parameters.
  • Read all results that the stored procedure returns using sqlsrv_next_result. This advice is taken from sqlsrv_prepare in the example at the bottom.

Here is the code:

$search = "qweryt";
$return = 123;

$tsql_callSP = "EXEC Intranet.CustomerSearch @Search=?, @Return=?";
$params = array( 
    array(&$search, SQLSRV_PARAM_IN),
    array(&$return, SQLSRV_PARAM_OUT),
    );

$stmt = sqlsrv_query($conn, $tsql_callSP, $params);
if ($stmt === false)
{
    echo "EXEC Intranet.CustomerSearch Failed\n";
    die( print_r( sqlsrv_errors(), true));
}
else
{
    while($res = sqlsrv_next_result($stmt))
    {
        // make sure all result sets are stepped through, 
        // since the output params may not be set until this happens
    }
}

echo $return;

Verified with PHP 5.4.28, SQL Server Express 2014, Microsoft driver for PHP SQLSRV 3.2

When you have a stored procedure that does only INSERT or UPDATE, i.e. doesn't return result of SELECT, then sqlsrv_next_result happen to be not needed to get values of the output parameters. If there is SELECT together with output parameters, it effectively means that procedure returns several result sets and you need to read all of them.

Actually, here is a very similar question.

Edit

If I understood you correctly, you want to retrieve the value of the OUTPUT parameter first. Then you want to retrieve the result of the SELECT statement.

As far as I understand, it is not possible. SQL Server returns result of SELECT statement in the first result set, which you can read using sqlsrv_fetch_array. Then you move to the second result set using sqlsrv_next_result. At this moment the value of the OUTPUT parameter is transferred to your PHP variable, because SQL Server sends the values of OUTPUT parameters in the last result set. After you call sqlsrv_next_result the first result set is no longer available when you do sqlsrv_fetch_array.

By the way, when you said

What is strange is if I create a stored procedure that is an INSERT or UPDATE statement the OUTPUT returns correctly.

, it makes perfect sense. When stored procedure has no SELECT statements and it has SET NOCOUNT ON;, then the only result set that is returned is for OUTPUT parameters, so your PHP variable is assigned with correct value without explicit calls to sqlsrv_next_result.

So, you have at least two options.

1) Read the full result of SELECT into a temporary array using sqlsrv_fetch_array. Then use sqlsrv_next_result to get the value of the OUTPUT parameter. Then process result of the first SELECT saved in your array based on the received value of the OUTPUT parameter.

2) Do not use OUTPUT parameter at all. With such parameter(s) SQL Server returns multiple result sets implicitly. Since you have multiple result sets any way, return two result sets explicitly. In your stored procedure at first do SELECT that returns just one row with one number, then your main SELECT:

ALTER PROCEDURE [Intranet].[CustomerSearch]
    @Search nvarchar(10)
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS
        (
            SELECT KeyCode
            FROM Autopart.dbo.Customer
            WHERE KeyCode = @Search
        )
    BEGIN
        SELECT CAST(1 AS int) AS ReturnCode;

        SELECT
            Customer.KeyCode
            ,Customer.X13
            ,Customer.Name
            ,Customer.Addra
            ,Customer.Addrb
            ,Customer.Addrc
            ,Customer.Addrd
            ,Customer.Addre
            ,Customer.PCode
            ,Customer.CTitle
            ,Customer.Climit
            ,Customer.Ptype
            ,Customer.Stel
            ,Customer.SCont
            ,Customer.ACont
            ,Customer.XString5
            ,Customer.Locked
            ,Customer.Email
            ,Customer.StopStatus
            ,CusNotes.Comment
        FROM
            Autopart.dbo.Customer
            LEFT OUTER JOIN Autopart.dbo.CusNotes ON Autopart.dbo.Customer.KeyCode = Autopart.dbo.CusNotes.Account
        WHERE
            (Customer.KeyCode = @Search)
            AND (CusNotes.Seqno = 1 OR CusNotes.Seqno IS NULL)
        ;

    END ELSE BEGIN

        SELECT CAST(0 AS int) AS ReturnCode;

        SELECT TOP 100
            KeyCode
            ,Name
            ,PCode
            ,Addra
            ,Addrb
            ,Addrc
        FROM
            AUTOPART.dbo.Customer
        WHERE
            KeyCode LIKE '%'+@Search+'%'
            OR Name LIKE '%'+@Search+'%'
            OR PCode LIKE '%'+@Search+'%'
        ;
    END
END

In PHP code call sqlsrv_fetch_array first time to read ReturnCode. Then call sqlsrv_next_result to switch to the second result set. Then call sqlsrv_fetch_array to read result of the main SELECT.

$search = "qweryt";

$tsql_callSP = "EXEC Intranet.CustomerSearch @ParamSearch=?";
$params = array
    (
    array(&$search, SQLSRV_PARAM_IN)
    );

$stmt = sqlsrv_query($conn, $tsql_callSP, $params);
if( $stmt === false )
{
    echo "EXEC Failed\n";
    die( print_r( sqlsrv_errors(), true));
}

echo "First result set:\n";
while ($row = sqlsrv_fetch_array($stmt))
{
    var_dump($row);
}

echo "Next result:\n";
$next_res = sqlsrv_next_result($stmt);
var_dump($next_res);

echo "Second result set:\n";
while ($row = sqlsrv_fetch_array($stmt))
{
    var_dump($row);
}

Upvotes: 6

user4404353
user4404353

Reputation:

This is due to a character encoding problem with PHP output. Try using 

header ('Content-type: text/html; charset=UTF-8');

or

header ('Content-type: text/html; charset=ISO-8859-1');

Also, you can check this nice article.

Upvotes: 0

Related Questions