对于未来的用户:这个问题的底部包含更正的工作代码。
我知道 Select * 不是最好的,但在这个示例中,我尝试从 php 调用存储过程并返回整个结果集,以便我可以在代码中循环遍历数组。
这是我当前的存储过程:
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.
/
编辑:对于未来的用户!工作代码如下。
存储过程:
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);