在不使用光标的情况下为每一行调用过程，并使用过程的结果设置行 [英] Call Procedure for each Row without using a cursor and set the row with the result of the procedure

问题描述

我有此过程:

CREATE PROC dbo.##HTMLtoMARKDOWN @text nvarchar(500),
                                 @returnText nvarchar(500) output

AS
BEGIN
    DECLARE @counter tinyint
    SET @counter=1

    WHILE CHARINDEX('**', @text, 1) > 0
    BEGIN
        SELECT @text = STUFF(@text, 
                    CHARINDEX('**', @text, 1), 
                    2, 
                    IIF(@counter%2=0,'<br><b>','</b>')),
                @counter = @counter + 1
    END
SET @returnText = @text
END
GO

可以像这样运行:

DECLARE @returnText nvarchar(500)
EXEC dbo.##HTMLtoMARKDOWN '**a** **b** **c**', @returnText output

我正在使用这种查询:

Select, IIF(IsUniversal=0,'TRUE','FALSE') as [Is Universal?],
    MarkdownMini as [Off Topic Reason]
From CloseAsOffTopicReasonTypes
group by IsUniversal, MarkdownMini

如果dbo.##HTMLtoMARKDOWN被声明为函数(CREATE FUNCTION dbo.HTMLtoMARKDOWN @text nvarchar(500)))，我可以这样写:

Ifdbo.##HTMLtoMARKDOWNwas declared as a function (CREATE FUNCTION dbo.HTMLtoMARKDOWN @text nvarchar(500))), I could have written this:

Select, IIF(IsUniversal=0,'TRUE','FALSE') as [Is Universal?],
        dbo.HTMLtoMARKDOWN(MarkdownMini) as [Off Topic Reason]
From CloseAsOffTopicReasonTypes
group by IsUniversal, MarkdownMini

不允许使用函数，那么如何通过一个临时过程来做这种事情呢?

I'm not allowed to use functions, so how I can do that kind of thing with a temporary procedure?

推荐答案

要使用存储过程更新行，您需要使用光标:

To update rows using a stored procedure, you need cursor:

    DEClARE @akey int, @text NVARCHAR(500),@retText NVARCHAR(500);
    DECLARE  c CURSOR LOCAL FAST_FORWARD FOR SELECT aid, MarkdownMini 
    FROM CloseAsOffTopicReasonTypes;
    OPEN c;
    FETCH NEXT FROM c into @akey, @text;
    WHILE @@FETCH_STATUS=0 BEGIN
        EXEC dbo.##HTMLtoMARKDOWN @TEXT, @retText output;
        UPDATE CloseAsOffTopicReasonTypes 
           SET MarkDown = @retText WHERE aid = @akey;
        FETCH NEXT FROM c into @akey, @text;
    END;
    DEALLOCATE c;

如果要返回记录集(如select)，则需要一个临时表或内存表:

If you intent to return a record set (like select), you need a temp table or in-memory table:

    DECLARE @TMP TABLE (akey int, MarkDown nvarchar(800) );
    SET NOCOUNT ON;
    DEClARE @akey int, @text NVARCHAR(500),@retText NVARCHAR(500);
    DECLARE  c CURSOR LOCAL FAST_FORWARD FOR SELECT aid, MarkdownMini 
           FROM CloseAsOffTopicReasonTypes;
    OPEN c;
    FETCH NEXT FROM c into @akey, @text;
    WHILE @@FETCH_STATUS=0 BEGIN
        EXEC dbo.##HTMLtoMARKDOWN @TEXT, @retText output;
        --UPDATE CloseAsOffTopicReasonTypes SET MarkDown = @retText WHERE aid = @akey;
        INSERT INTO @TMP (akey, MarkDown) values(@akey, @retText);
        FETCH NEXT FROM c into @akey, @text;
    END;
    DEALLOCATE c;
    SET NOCOUNT OFF;
    SELECT * FROM @TMP;

如果要将行返回给C#，PHP之类的调用方，并且还希望将上述行放入一个存储过程中，则需要SET NOCOUNT ON/OFF.

The SET NOCOUNT ON/OFF are required if you want to return the row to a caller such as C#, PHP, where you also want to make above lines into one stored procedure.

这篇关于在不使用光标的情况下为每一行调用过程，并使用过程的结果设置行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！