Select does not return rows after insert in stored procedure

Question

I am trying to insert row into 2 tables and then do select in a stored procdure in SQL Server 2012, and for some reason I get 0 rows in php (sqlsrv) (in navicat I do get 1 row as should)

ALTER PROCEDURE [dbo].[Register]
@FacebookId AS INT,
@SessionKey AS VARCHAR(255),
@Name AS NVARCHAR(255),
@Gender AS INT = 0,
@ProfilePicture AS VARCHAR(255)
AS
BEGIN
    IF NOT EXISTS(SELECT Id FROM Users WHERE FacebookId = @FacebookId)
        BEGIN
            INSERT INTO Users(FacebookId,SessionKey) VALUES (@FacebookId,@SessionKey)
            DECLARE @Id AS INT
            SET @Id = scope_identity()
            INSERT INTO UserData(Id,Name,Gender,ProfilePicture) VALUES (scope_identity(),@Name,@Gender,@ProfilePicture)
            SELECT 1 AS success
        END
    ELSE
        BEGIN
            SELECT 0 AS success
        END
END

php code:

        function Register($facebookId,$sessionKey,$name,$gender,$profilePicture)
        {
             return     SingleResponseRequest('Register',array('@FacebookId','@SessionKey','@Name','@Gender','@ProfilePicture'),
                                  array($facebookId,$sessionKey,$name,$gender,$profilePicture),'success');

        }


        function SingleResponseRequest($procedureName,$procedureValues,$valuesArray,$returnColumn)
        {
            require_once __DIR__ . '/Configuration/Connection.php';
            $db = new Connection();
            $values = '';

            foreach($procedureValues as $value)
                $values .= $value . ' = ? ,';

            $values = substr($values,0,strlen($values)-1);
            $query = "EXEC $procedureName $values";
            $res = $db->Query($query,$valuesArray);
            $row_count = sqlsrv_num_rows( $res );
            echo 'rows: '. $row_count;

            if ($row_count === false)
                echo "Error in retrieving row count.";

            $row = sqlsrv_fetch_array( $res, SQLSRV_FETCH_ASSOC);

            return $row[$returnColumn];
        }

   public function Query($query,$params)
    {
        for($i = 0 ; $i < count($params); $i++)
        {
            $params[$i] = $this->ms_escape_string($params[$i]);
            $params[$i] = utf8_encode($params[$i]);
        }

        $options =  array( "Scrollable" => SQLSRV_CURSOR_CLIENT_BUFFERED );

        $result = sqlsrv_query($this->connection, $query, $params,$options);
        if ( $result === false )
        {
            echo "Error in statement execution.<br>";
            echo $query;
            die( print_r( sqlsrv_errors(), true));
        }
        return $result;
    }

I do get 0 if it goes to the else condition, what could be the problem?

In the management studio of sql server output the select you have on your if clause on sql to check it that @FacebookId exists. For me that seems to be the problem. The rows are inserted on both tables? — Tiago Reis
– Tiago Reis, Commented Jul 11, 2014 at 15:42
Since the rows are inserted...what exactly is the question? BTW, you could make this a bit simpler by using the OUTPUT statement in your initial insert. — Sean Lange
– Sean Lange, Commented Jul 11, 2014 at 15:45

Rahul K A · Accepted Answer · 2017-01-04 11:19:55Z

1

Hi I had same issue like this. I solved it . You just place "SET NOCOUNT ON" on your stored procedure

for example

CREATE procedure mysampleprocedure

AS BEGIN TRANSACTION TRANS

set nocount on
insert into sampletable(id,data) values
(
101,
'abc'
);

SELECT count(*) AS id FROM sampletable AS id;

IF @@ERROR > 0
BEGIN 
 ROLLBACK TRANSACTION TRANS
 end
 ELSE
 BEGIN
 COMMIT TRANSACTION TRANS
 end
GO

answered Jan 4, 2017 at 11:19

Rahul K A

3324 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select does not return rows after insert in stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related