Pretty much a SQL programming newbie so ...

I have this code and I'm getting an error on the Update statement. I'm not
sure how to represent the '' in the string to be executed. Is there a
function or a system-wide constant?

The error reported  is

Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ''.

Although it is reporting line 1 I'm assuming that the problem is in my
construction of the Update statement.

Here's the code being executed

 USE MyDB
 DECLARE TempCursor CURSOR
 FOR  SELECT  c.TABLE_NAME, c.COLUMN_NAME
  FROM  INFORMATION_SCHEMA.COLUMNS c
   INNER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME=t.TABLE_NAME
  WHERE c.COLUMN_NAME Like 'Col%'
  AND t.TABLE_NAME LIKE 'Table%'
 OPEN TempCursor

 DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128)
 FETCH NEXT From TempCursor INTO @TableName, @ColumnName
 WHILE (@@FETCH_STATUS <> -1)
 BEGIN
  IF (@@FETCH_STATUS <> -2)
  BEGIN
   EXEC
    (
     'UPDATE ' + @TableName +
     ' SET ' + @ColumnName  + '= NULL' +
         ' WHERE ' + @ColumnName + '= '''
    )
  END
  FETCH NEXT From TempCursor INTO @TableName, @ColumnName
 END
 DEALLOCATE TempCursor

If I'm barking up the wrong tree then please feel free to correct me ...
basically for all the user tables in the db and specific cols within those
tables, I'm wanting to set empty strings back to null

Thanks vm

Simon

declare @str nvarchar (2000)

...

SET @str =      'UPDATE ' + @TableName +
     ' SET ' + @ColumnName  + '= NULL' +
         ' WHERE ' + @ColumnName + '= '''''

EXEC (@str)

Also, you forgot to double the single-quotes at the end of the line with the
WHERE part.  I corrected it above.

--
   Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON   Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau

Pretty much a SQL programming newbie so ...

I have this code and I'm getting an error on the Update statement. I'm not
sure how to represent the '' in the string to be executed. Is there a
function or a system-wide constant?

The error reported  is

Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ''.
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ''.

Although it is reporting line 1 I'm assuming that the problem is in my
construction of the Update statement.

Here's the code being executed

 USE MyDB
 DECLARE TempCursor CURSOR
 FOR  SELECT  c.TABLE_NAME, c.COLUMN_NAME
  FROM  INFORMATION_SCHEMA.COLUMNS c
   INNER JOIN INFORMATION_SCHEMA.TABLES t ON c.TABLE_NAME=t.TABLE_NAME
  WHERE c.COLUMN_NAME Like 'Col%'
  AND t.TABLE_NAME LIKE 'Table%'
 OPEN TempCursor

 DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128)
 FETCH NEXT From TempCursor INTO @TableName, @ColumnName
 WHILE (@@FETCH_STATUS <> -1)
 BEGIN
  IF (@@FETCH_STATUS <> -2)
  BEGIN
   EXEC
    (
     'UPDATE ' + @TableName +
     ' SET ' + @ColumnName  + '= NULL' +
         ' WHERE ' + @ColumnName + '= '''
    )
  END
  FETCH NEXT From TempCursor INTO @TableName, @ColumnName
 END
 DEALLOCATE TempCursor

If I'm barking up the wrong tree then please feel free to correct me ...
basically for all the user tables in the db and specific cols within those
tables, I'm wanting to set empty strings back to null

Thanks vm

Simon

-----------------------------------------------Reply-----------------------------------------------