CODEWITHSUNDEEP
sqlsql-serversql-updatecursor
Wes Palmer
Wes Palmer

Reputation: 880

Update Table with a cursor and varables

I want to use a cursor to update a table I have, but I haven't had any luck. That's why I wanted to use a cursor in which I don't have to spend hours of writing 100's of update statements. Here is my current code:

DECLARE @COLUMN_NAME VARCHAR(10)
DECLARE A_Cursor CURSOR
    FOR
        SELECT column_name
        FROM TPP_DB.INFORMATION_SCHEMA.columns
        WHERE table_name = 'Master File'
            AND column_name LIKE 'F%'
            AND LEN(column_name) <= 4
            AND column_name NOT IN ('F61', 'F62') --FOR UPDATE OF [TPP_DB].[dbo].[Master File]

OPEN A_Cursor
FETCH NEXT FROM A_Cursor INTO @COLUMN_NAME

WHILE @@fetch_status = 0
BEGIN

    PRINT @COLUMN_NAME

    UPDATE TPP_DB.dbo.[Master File]
    SET @COLUMN_NAME = NULL
    WHERE @COLUMN_NAME = ''

    FETCH NEXT FROM A_Cursor INTO @COLUMN_NAME

END

CLOSE A_Cursor
DEALLOCATE A_Cursor

The main problem I am having is that my variable @COLUMN_NAME isn't getting loaded in the update statement but when I print it, I get the correct value. I would like the variable to be read into the SET and WHERE clause so that I can update all the tables I need at once. Any other suggestions would be wonderful as well.

Upvotes: 2

Views: 131

Answers (2)

Devart
Devart

Reputation: 121922

USE TPP_DB
GO

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = (
    SELECT '
UPDATE ' + QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.name) + '
SET ' + QUOTENAME(c.name) + ' = NULL
WHERE ' + QUOTENAME(c.name) + ' = ''''' 
    FROM sys.columns c
    JOIN sys.objects o ON c.[object_id] = o.[object_id]
    WHERE o.name = 'MasterFile' 
        AND c.name LIKE 'F%' 
        AND c.name NOT IN ('F61','F62')
        AND o.[type] = 'U'
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)')

PRINT @SQL
EXEC sys.sp_executesql @SQL

Output -

UPDATE [dbo].[tbl]
SET col = NULL
WHERE col = ''
...

Upvotes: 1

M.Ali
M.Ali

Reputation: 69524

You would need dynamic sql for it something like.....

DECLARE @Sql NVARCHAR(MAX) , @COLUMN_NAME SYSNAME;
DECLARE A_Cursor CURSOR FOR 

SELECT COLUMN_NAME 
FROM TPP_DB.INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'Master File' 
    AND COLUMN_NAME LIKE 'F%' 
    AND LEN(COLUMN_NAME) <= 4 
    AND COLUMN_NAME NOT IN ('F61','F62') --FOR UPDATE OF [TPP_DB].[dbo].[Master File]

OPEN A_Cursor
FETCH NEXT FROM A_Cursor  INTO @COLUMN_NAME

WHILE @@FETCH_STATUS = 0
BEGIN 

PRINT @COLUMN_NAME

SET @Sql = N' UPDATE TPP_DB.dbo.[Master File]
               SET ' + QUOTENAME(@COLUMN_NAME) + ' = NULL
              WHERE ' + QUOTENAME(@COLUMN_NAME) + ' = '''' '

  Exec sp_executesql @Sql

FETCH NEXT FROM A_Cursor INTO @COLUMN_NAME

END


CLOSE A_Cursor
DEALLOCATE A_Cursor

Upvotes: 1

Related Questions