Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

SQL Server - entering Empty Strings


Hi

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

Build you exec string separately and then exec it:

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

"Simon Woods" <simon.woodsUsel@virgin.net> wrote in message

news:%23XuskreoHHA.5052@TK2MSFTNGP04.phx.gbl...
Hi

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-----------------------------------------------

Thanks Tom ... that helped.

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc