4

For future users: The bottom of this question contains the corrected working code.

I know Select * is not the best, but in this example, I am trying to call a stored procedure from php and return the ENTIRE result set so I can loop through the array in my code.

Here is my current stored procedure:

USE [hanoncs_AskMe]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON;
GO

CREATE PROCEDURE [hanoncs_hanoncs].[CommentsTemp] 
    @QuestionID INT


 AS
 BEGIN
    BEGIN TRANSACTION

        IF Object_id('#viewquestioncomments', 'U') IS NOT NULL DROP TABLE #viewquestioncomments;


        CREATE TABLE #viewquestioncomments 
                     ( 
                                  commentid INT DEFAULT ((0)), 
                                  userid    INT DEFAULT ((0)), 
                                  comment   VARCHAR(max) DEFAULT '', 
                                  datemodified SMALLDATETIME, 
                                  username NVARCHAR(200) DEFAULT '', 
                                  points   INT DEFAULT ((0)) 
                     );


                     INSERT INTO #viewquestioncomments 
                    ( 
                                commentid, 
                                userid, 
                                comment, 
                                datemodified 
                    ) 
        SELECT id, 
               userid, 
               comment, 
               datemodified 
        FROM   hanoncs_askme.hanoncs_hanoncs.comments 
        WHERE  postid=1
        AND    status=1;


        UPDATE #viewquestioncomments 
        SET       username = m.username 
        FROM      #viewquestioncomments c 
        LEFT JOIN hanoncs_securelogin.hanoncs_hanoncs.members m 
        ON        m.id = c.userid;


        UPDATE #viewquestioncomments 
        SET    points = 
               ( 
                      SELECT Count(*) 
                      FROM   hanoncs_askme.hanoncs_hanoncs.commentvotes 
                      WHERE  postid=c.commentid) 
        FROM   #viewquestioncomments c;


        SELECT * 
        FROM   #viewquestioncomments;


        IF @@ERROR != 0
        ROLLBACK TRANSACTION
        ELSE
            COMMIT TRANSACTION
 END

In MS SQL Management Studio, this returns the temp table like I want: 

EXEC [hanoncs_hanoncs].[CommentsTemp] @QuestionID = 1

I am calling it in php with:

    $stmt = $PDO->prepare('EXEC [hanoncs_hanoncs].[CommentsTemp] @QuestionID = ?');
    $stmt->bindParam(1, $QuestionID, PDO::PARAM_INT);
    $stmt->execute();
    $rows6 = $stmt->fetch(PDO::FETCH_BOTH);

The error I get is:

PDOException  SQLSTATE[IMSSP]: The active result for the query contains no fields.

/

EDIT:For future users!!! The working code is below.

Stored Procedure:

USE [hanoncs_AskMe]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [hanoncs_hanoncs].[CommentsTemp] 
    @QuestionID INT OUTPUT


 AS

 BEGIN
 SET NOCOUNT ON;
    BEGIN TRANSACTION

        IF Object_id('#viewquestioncomments', 'U') IS NOT NULL DROP TABLE #viewquestioncomments;


        CREATE TABLE #viewquestioncomments 
                     ( 
                                  CommentID INT DEFAULT ((0)), 
                                  UserID    INT DEFAULT ((0)), 
                                  Comment   VARCHAR(max) DEFAULT '', 
                                  DateModified SMALLDATETIME, 
                                  UserName NVARCHAR(200) DEFAULT '', 
                                  Points   INT DEFAULT ((0)),
                                  Avatar nvarchar(200) DEFAULT ''
                     );


                     INSERT INTO #viewquestioncomments 
                    ( 
                                commentid, 
                                userid, 
                                comment, 
                                datemodified 
                    ) 
        SELECT id, 
               userid, 
               comment, 
               datemodified 
        FROM   hanoncs_askme.hanoncs_hanoncs.comments 
        WHERE  postid=1
        AND    status=1;


        UPDATE #viewquestioncomments 
        SET       username = m.username , Avatar = m.avatar
        FROM      #viewquestioncomments c 
        LEFT JOIN hanoncs_securelogin.hanoncs_hanoncs.members m 
        ON        m.id = c.userid;


        UPDATE #viewquestioncomments 
        SET    points = 
               ( 
                      SELECT Count(*) 
                      FROM   hanoncs_askme.hanoncs_hanoncs.commentvotes 
                      WHERE  postid=c.commentid) 
        FROM   #viewquestioncomments c;


        SELECT * 
        FROM   #viewquestioncomments;


        IF @@ERROR != 0
        ROLLBACK TRANSACTION
        ELSE
            COMMIT TRANSACTION
 END

PHP:

   $stmt = $PDO->prepare('EXEC [hanoncs_hanoncs].[CommentsTemp] @QuestionID = ?');
   $stmt->bindParam(1, $QuestionID, PDO::PARAM_INT);
   $stmt->execute();
   $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
M H
  • 2,179
  • 25
  • 50
  • There is nothing wrong with `SELECT *`, for some reason the stupidity that it's "bad" caught on. If it weren't useful, it would never exist. If it makes sense to use, then use it. However, the problem you're having is [explained in this question](http://stackoverflow.com/questions/16735898/the-active-result-contains-no-fields-using-pdo-with-ms-sql). Have you tried that maybe? – Mjh Jul 07 '15 at 15:26

1 Answers1

5

Try adding SET NOCOUNT ON; inside the stored procedure (not above where you are creating the procedure).

Add it after the AS BEGIN and before the BEGIN TRANSACTION;

Like so:

CREATE PROCEDURE [hanoncs_hanoncs].[CommentsTemp] 
    @QuestionID INT
 AS  BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION

From what it looks like to me, your result set will contain the row counts of affected rows first, then the actual data you want back.

LaraRaraBoBara
  • 628
  • 1
  • 6
  • 15