Call Procedure for each Row without using a cursor

问题:

I have this 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

Which can be run like this:

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

I'm using this kind of query:

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

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?

回答1:

This one works by applying the stored procedure to the distinct reasons rather than processing the whole set.

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 @counter=1
    WHILE CHARINDEX('*', @text, 1) > 0
    BEGIN
        SELECT @text = STUFF(@text, 
                    CHARINDEX('*', @text, 1), 
                    1, 
                    IIF(@counter%2=0,'<br><i>','</i>')),
                @counter = @counter + 1
    END
    -- SET @returnText = @text
    SET @returnText = @text
END
GO

DECLARE @returnText nvarchar(500)

--taken from http://meta.stackexchange.com/a/237237/242800
;with ReasonsPerPost as
(
   -- get the count of each close/flag reason per post
   select TOP 100 PERCENT -- Only use with clustered index.
      Posts.Id,
      PendingFlags.CloseReasonTypeId,
      IIF(CloseReasonTypeId<>102,CloseReasonTypes.Name,MarkdownMini) as Name,
      count(PendingFlags.CloseReasonTypeId) as TotalByCloseReason
   from Posts
      INNER JOIN PendingFlags on PendingFlags.PostId = Posts.Id
      INNER JOIN CloseReasonTypes on CloseReasonTypes.Id=PendingFlags.CloseReasonTypeId
      LEFT OUTER JOIN CloseAsOffTopicReasonTypes on CloseAsOffTopicReasonTypes.id=PendingFlags.CloseAsOffTopicReasonTypeId
   where Posts.ClosedDate IS NULL -- The question is not closed.
          and PendingFlags.FlagTypeId in (14,13) -- Exclude reopen votes
   group by Posts.id, CloseReasonTypes.Name, MarkdownMini, PendingFlags.CloseReasonTypeId
   order by TotalByCloseReason desc
),
TopPerPost as
(
  -- create a row number to order the results by the close reason totals
   select Id,
      CloseReasonTypeId,
      Name,
      ReasonsPerPost.TotalByCloseReason,
      row_number() over(partition by Id order by TotalByCloseReason desc) seq
   from ReasonsPerPost
   where Name is NOT NULL
)



select TOP ##Limit:int?38369## -- This number may grow, or get removed the day the server will have enough RAM.
   Posts.Id as [Post Link], -- Question title.
   Count(PendingFlags.PostId) as [Number of pending flags], -- Number of pending flags per questions.
   TopPerPost.Name as [The most common vote reason],
   Posts.OwnerUserId as [User Link], -- Let click on the colum to see if the same user ask off-topic questions often.
   Reputation as [User Reputation], -- Interesting to see that such questions are sometimes asked by high rep users.
   Posts.Score as [Votes], -- Interesting to see that some questions have more than 100 upvotes.
   Posts.AnswerCount as [Number of Answers], -- I thought we shouldn't answer on off-topic post.
   Posts.ViewCount,
   Posts.FavoriteCount as [Number of Stars], -- Some questions seems to be very helpfull :) .
   Posts.CreationDate as [Asked on], -- The older is the question, the more is the chance that flags on them can't get reviewed.
   Posts.LastActivityDate as [last activity], -- Similar effect as with Posts.CreationDate.
   Posts.LastEditDate as [modified on]
into #results 
from Posts
   INNER JOIN PendingFlags on PendingFlags.PostId = Posts.Id
   LEFT OUTER JOIN Users on Users.id = posts.OwnerUserId
   LEFT OUTER JOIN TopPerPost on Posts.id=TopPerPost.id
where seq=1
group by Posts.id, Posts.OwnerUserId, TopPerPost.Name, Reputation, Posts.Score, Posts.FavoriteCount, Posts.AnswerCount, Posts.CreationDate, Posts.LastActivityDate, Posts.LastEditDate, Posts.ViewCount
order by [Number of pending flags] desc, [The most common vote reason], Score desc, Reputation desc, FavoriteCount desc, ViewCount desc, Posts.CreationDate asc, LastActivityDate, LastEditDate -- Questions with more flags have more chance to get them handled, and the higher is the probabilty that the question is off-topic (since several users already reviewed the question).

select distinct [The most common vote reason] into #reasons from #results
ALTER TABLE #reasons 
ADD id INT IDENTITY(1,1), html nvarchar(500)

create nonclustered index results_reasons_index
on #results ([The most common vote reason]);

create unique nonclustered index reasons_index
on #reasons ([The most common vote reason]);

declare @id int
declare @maxId as int
declare @markdown as nvarchar(500)
declare @html as nvarchar(500)
select @maxId = max(id) from #reasons 
set @id = 0

while ( @id < @maxId )
begin
    set @id = @id + 1
    select @markdown = [The most common vote reason] from #reasons where id = @id
    exec dbo.##HTMLtoMARKDOWN @text = @markdown, @returnText = @html output
    update #reasons set html = @html where id = @id
end

update #results set [The most common vote reason] = #reasons.html
from #results 
inner join #reasons 
on #results.[The most common vote reason] 
=  #reasons.[The most common vote reason] 

select * from #results

回答2:

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;

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;

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.