SQL Server SQL stored procedure doesn't stop executing

zzoitvuj 于 2023-03-28 发布在其他

关注(0)|答案(1)|浏览(116)

I have a stored procedure that executes indefinitely. I can't see where I have created an infinite loop or anything. Any suggestions as to why this keeps executing?

ALTER PROCEDURE sp_RemovePaddingPicture_Name
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @test nvarchar(50)

    DECLARE cur CURSOR FOR
        SELECT Picture_Name
        FROM PEOPLEGENERAL

    OPEN cur
    FETCH FROM cur

    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE PEOPLEGENERAL
        SET PICTURE_NAME = RTRIM(PICTURE_NAME)
        WHERE CURRENT of cur

        FETCH NEXT FROM cur
    END

    CLOSE cur
    DEALLOCATE cur
END

I created this stored procedure to update a column. It keeps executing.

sql-server

来源：https://stackoverflow.com/questions/75858464/sql-stored-procedure-doesnt-stop-executing

1条答案

按热度按时间

tcomlyy61#

You are not doing anything with the value returned from the cursor for one thing.

ALTER PROCEDURE proc_RemovePaddingPicture_Name -- used a different prefix
AS
BEGIN
    DECLARE @picName nvarchar(50) -- meanfull name for variable

    DECLARE cur CURSOR FOR
        SELECT Picture_Name
        FROM PEOPLEGENERAL
        WHERE PICTURE_NAME != RTRIM(PICTURE_NAME) -- added to only update records with a difference

    
    OPEN cur
    FETCH NEXT FROM cur INTO @picName -- assign returned value to variable
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE PEOPLEGENERAL
        SET PICTURE_NAME = RTRIM(PICTURE_NAME)
        WHERE @picName = Picture_Name -- use of assigned variable
    
        FETCH NEXT FROM cur INTO @picName -- assign returned value to variable
    END

    CLOSE cur
    DEALLOCATE cur
END

That all said you could also just do this with one statement provided the table is not that large that it would cause an issue.

UPDATE PEOPLEGENERAL SET PICTURE_NAME = RTRIM(PICTURE_NAME)

or if there are only a limited number of records that should be touched because the value is the same.

UPDATE PEOPLEGENERAL 
SET PICTURE_NAME = RTRIM(PICTURE_NAME) 
WHERE PICTURE_NAME != RTRIM(PICTURE_NAME)

赞(0）回复(0）举报 2023-03-28

我来回答

SQL Server SQL stored procedure doesn't stop executing

1条答案

相关问题

热门标签

最新问答