执行更新时存储过程失败 [英] Stored Procedure fails when performing Update

问题描述

我正在使用适用于 PHP 的 SQL Server 驱动程序连接到 SQL Server 2008 Express.现在，我正在尝试用存储过程替换所有 SELECT、UPDATE 和 INSERT 语句.这适用于只包含 SELECT 语句的 SP.但是现在我尝试进行更新，并且不断收到错误消息直接执行 SQL；没有游标.".我可以使用相同的参数值从 Management Studio 调用 SP.

I'm using the SQL Server Driver for PHP to connect to an SQL Server 2008 Express. Right now, I'm trying to replace all SELECT, UPDATE and INSERT statements by stored procedures. This is working fine for SPs that just contain a SELECT statement. But now I tried to do one with an update, and I keep getting the error message "Executing SQL directly; no cursor.". I can call the SP fine from Management Studio with the same parameter values.

有什么想法吗?

干杯亚历克斯

这是一个更新程序.有趣的是，该过程实际上执行得很好，并像预期的那样更新了数据.但是还是返回错误，导致异常.

here's one update procedure. The funny part is, the procedure is actually executed fine and updates the data like it's supposed to. But it still returns an error, resulting in an exception.

首先，失败的PHP代码:

First, the PHP code that fails:

if (! $this->Result = sqlsrv_query($this->Conn, $strQuery, $arrParameters, array("Scrollable"=>SQLSRV_CURSOR_STATIC)))
{
    $this->sendErrorMail($strQuery, $arrParameters);
    throw new Exception(4001);
}

SQL

USE [testsite]  
GO  
/****** Object:  StoredProcedure [dbo].[Items_countDownload]    Script Date: 09/09/2010 18:03:28 ******/  
SET ANSI_NULLS ON  
GO  
SET QUOTED_IDENTIFIER ON  
GO  
-- =============================================  
-- Author:      Alexis Hildebrandt  
-- Create date: 2010-09-09  
-- Description: Increases the download count by 1  
-- =============================================  
ALTER PROCEDURE [dbo].[Items_countDownload]  
    @Id INT  
AS  
BEGIN  
    -- SET NOCOUNT ON added to prevent extra result sets from  
    -- interfering with SELECT statements.  
    SET NOCOUNT ON;  

    DECLARE @DownloadCount INT = 0, @MaxCount INT = 0, @Id2 INT = 0  

    DECLARE itemCursor CURSOR SCROLL
    FOR
        SELECT Id, Downloads
        FROM Items
        WHERE Id = @Id
        OR SKU IN 
        (
            SELECT SKU FROM Items WHERE Id = @Id
        )
    FOR UPDATE OF Downloads

    OPEN itemCursor

    FETCH NEXT FROM itemCursor
    INTO @Id, @DownloadCount;

    -- Find the largest Download count across all versions of the item
    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @MaxCount < @DownloadCount
            SET @MaxCount = @DownloadCount;
        FETCH NEXT FROM itemCursor
        INTO @Id, @DownloadCount;
    END

    -- Increase the download count by one for all versions
    FETCH FIRST FROM itemCursor
    INTO @Id, @DownloadCount;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE Items 
        SET Downloads = @MaxCount + 1
        WHERE CURRENT OF itemCursor
        FETCH NEXT FROM itemCursor
        INTO @Id, @DownloadCount;
    END

    CLOSE itemCursor;
    DEALLOCATE itemCursor;
END

推荐答案

尝试从调用中删除 "Scrollable"=>SQLSRV_CURSOR_STATIC 选项.您正在调用的过程不会向 PHP 代码返回任何打开的游标.

Try removing "Scrollable"=>SQLSRV_CURSOR_STATIC option from the call. The procedure you're calling doesn't return any open cursors to PHP code.

这篇关于执行更新时存储过程失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！