SQL Server while loop and dynamic SQL

Question

I am trying to execute the dynamic SQL from SQL Server while loop. When the only print statement is executed query prints in the correct format but does not execute dynamic SQL with execute() or SP_EXECUTESQL. Please suggest. Code:

WHILE( @count > 0 )
BEGIN 
    SELECT 
        @minID = MinID,
        @maxID = MaxID
    FROM
        IDRange
    WHERE
        ID = @count

    SET @QueryString = ' UPDATE 
                        SD  WITH(TABLOCk)
                            SET a = S4H.ID
                        FROM 
                            A (nolock) S4H
                            INNER JOIN B SD on S4H.col = SD.col AND S4H.col1 = SD.col1
                        WHERE 
                            SD.ID between ' + convert (varchar,@minID )+' AND '+convert (varchar,@maxID )+' AND
                            S4H.ID <= SD.ID AND 
                            SD.ID <= S4h.ROWID' 
                            SET @count= @count - 1' 
    print @QueryString
    EXECUTE (@QueryString)
    EXECUTE sp_executesql @QueryString, N'@minID INT,@maxID INT', @minID = @minID,@maxID= @maxID
    --EXEC SP_EXECUTESQL @QueryString
    --SELECT @Rcount= @@Rowcount

    SET @count= @count - 1
END

END

dbajtr · Accepted Answer · 2017-09-04 08:19:37Z

2

There is an open ' at the end of SET @count= @count, remove that.

Use EXEC (@QueryString) to execute it, so it will look this this:

WHILE(@count > 0)
    BEGIN
        SELECT
            @minID = MinID,
            @maxID = MaxID
        FROM IDRange
        WHERE ID = @count;
        SET @QueryString = ' UPDATE 
                        SD  WITH(TABLOCk)
                            SET a = S4H.ID
                        FROM 
                            A (nolock) S4H
                            INNER JOIN B SD on S4H.col = SD.col AND S4H.col1 = SD.col1
                        WHERE 
                            SD.ID between '+CONVERT(VARCHAR, @minID)+' AND '+CONVERT(VARCHAR, @maxID)+' AND
                            S4H.ID <= SD.ID AND 
                            SD.ID <= S4h.ROWID';
        PRINT @QueryString;
        EXEC (@QueryString);

        SET @count = @count - 1;
    END;

edited Sep 4, 2017 at 8:19

answered Sep 4, 2017 at 6:53

dbajtr

2,0442 gold badges16 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alfaiz Ahmed · Accepted Answer · 2017-09-04 07:31:28Z

-- Dynamic Query closing was not proper SET @count= @count - 1' Repeated twice which is not proper

WHILE( @count > 0 )
BEGIN 

    SELECT @minID = MinID, @maxID = MaxID
    FROM  IDRange
    WHERE ID = @count

    SET @QueryString = ' UPDATE 
                        SD  WITH(TABLOCk)
                            SET a = S4H.ID
                        FROM 
                            A (nolock) S4H
                            INNER JOIN B SD on S4H.col = SD.col AND S4H.col1 = SD.col1
                        WHERE 
                            SD.ID BETWEEN ' + convert (varchar,@minID )+' AND '+convert (varchar,@maxID )+' AND
                            S4H.ID <= SD.ID AND 
                            SD.ID <= S4h.ROWID' 

    PRINT    @QueryString
    EXECUTE (@QueryString)
    SET @count= @count - 1
END

Collectives™ on Stack Overflow

SQL Server while loop and dynamic SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related