CODEWITHSUNDEEP
sql-server
arcee123
arcee123

Reputation: 211

Use a cursor to dynamically build SQL statement in stored procedure shows infinite loop

I am trying to build a dynamic SQL statement, which removes quotes from all values from all columns in a given file->db landing table.

Something similar to:

Update table 
set col1 = replace(col1, '"', ''), 
    col2 = replace(col2, '"', ''), ....

I am using this script to dynamically build the statement.

declare @query As varchar(1000)
declare @tablename as varchar(50)
declare @field as VARCHAR(50)

set @tablename = 'Contact'
set @query = 'Update [buyerhero_staging].[dbo].[Contact] SET '; 

DECLARE RecSet CURSOR FOR
    SELECT 
        CONCAT(COLUMN_NAME, '=REPLACE(,', COLUMN_NAME, ',''"'', '''')') as setcol 
    FROM  
        INFORMATION_SCHEMA.COLUMNS
    WHERE 
        TABLE_NAME = @tablename;

OPEN RecSet

FETCH NEXT FROM RecSet INTO @field

WHILE @@FETCH_STATUS = 0
BEGIN  
    SET @query = CONCAT(@query, ' ',@field, ', ')
END

CLOSE RecSet
DEALLOCATE RecSet

SELECT @query

The problem is that this script runs in an infinite loop, and I can't tell why. Especially since the select statement runs subsecond returns.

What am I missing?

Thanks

Upvotes: 1

Views: 84

Answers (1)

S3S
S3S

Reputation: 25112

You are missing a fetch next inside the begin block. Thus, your cursor value never changes. It's like forgetting a I = I++ or I = I +1 in simple loops with counters

...
WHILE @@FETCH_STATUS = 0
    BEGIN  
        SET @query = CONCAT(@query, ' ',@field, ', ')
        FETCH NEXT FROM RecSet INTO @field
    END
CLOSE RecSet
...

Upvotes: 1

Related Questions