从MS SQL中的PHP PDO存储过程返回Select * [英] Return Select * from PHP PDO Stored Procedure In MS SQL

问题描述

供将来的用户使用:此问题的底部包含更正的工作代码.

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

我知道Select *并不是最好的选择，但是在此示例中，我试图从php调用存储过程并返回整个结果集，以便我可以遍历代码中的数组.

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.

这是我当前的存储过程:

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

在MS SQL Management Studio中，这将返回临时表，就像我想要的那样:

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

我正在php中使用以下命令调用

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

我得到的错误是:

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

/

供将来的用户使用！！！工作代码如下.

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

存储过程:

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);  

推荐答案

尝试添加SET NOCOUNT ON；在存储过程内部(不在上面创建过程的位置).

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

在AS BEGIN之后之后和之前 BEGIN TRANSACTION中添加它；

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

像这样:

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.

这篇关于从MS SQL中的PHP PDO存储过程返回Select *的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！