Why does this code only work if executed with other statements?

• caspersql

  SSCrazy

  Points: 2276

  More actions

  September 20, 2013 at 9:13 am

  #277327

  I'm trying to run a check for column existence prior to updating a column. My sample code is as follows:

  -- DROP AND CREATE Source table

  IF

  OBJECT_ID(N'dbo.DoesNotIncludeColumn') IS NOT NULL

  AND EXISTS (SELECT 1 FROM sys.objects WHERE name = 'DoesNotIncludeColumn' and TYPE = 'U' )

  DROP TABLE dbo.DoesNotIncludeColumn

  GO

  CREATE TABLE DoesNotIncludeColumn

  (

  Column1 INT PRIMARY KEY NOT NULL

  ,Column2 nvarchar(4000) NULL

  )

  -- DROP AND CREATE Destination table

  IF

  OBJECT_ID(N'dbo.DestinationTable') IS NOT NULL

  AND EXISTS (SELECT 1 FROM sys.objects WHERE name = 'DestinationTable' and TYPE = 'U' )

  DROP TABLE dbo.DestinationTable

  GO

  CREATE TABLE DestinationTable

  (

  SourceColumn1 INT PRIMARY KEY NOT NULL

  ,SourceColumn2 nvarchar(4000) NULL

  )

  INSERT INTO DoesNotIncludeColumn

  (Column1

  ,Column2

  )

  VALUES (

  254

  ,'Source table text string'

  )

  INSERT INTO DestinationTable

  (

  SourceColumn1

  ,SourceColumn2

  )

  VALUES

  (

  254

  ,'Destination table text string'

  )

  IF EXISTS

  (

  SELECT 1

  FROM sys.objects objz

  INNER JOIN sys.columns colz

  ON objz.object_id = colz.object_id

  WHERE SCHEMA_NAME(objz.schema_id) = 'dbo'

  AND objz.name = N'DoesNotIncludeColumn'

  AND colz.name = N'ThisColumnDoesNotExist'

  )

  BEGIN

  UPDATE e

  SET

  e.SourceColumn2 = t.ThisColumnDoesNotExist

  FROM DoesNotIncludeColumn t

  JOIN DestinationTable e

  ON t.Column1 = e.SourceColumn1

  END

  As I've written it, the code works as expected.

  However, if I separately run the last part (from 'If Exists down) on its own, I get a message stating

  Msg 207, Level 16, State 1, Line 14

  Invalid column name 'ThisColumnDoesNotExist'.

  Is there something I can do to run the last bit separately?

• Mark Cowne

  One Orange Chip

  Points: 26976

  More actions

  September 20, 2013 at 9:19 am

  #1651959

  You can to do it dynamically

  EXEC('

  UPDATE e

  SET

  e.SourceColumn2 = t.ThisColumnDoesNotExist

  FROM DoesNotIncludeColumn t

  JOIN DestinationTable e

  ON t.Column1 = e.SourceColumn1')

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• caspersql

  SSCrazy

  Points: 2276

  More actions

  September 20, 2013 at 9:24 am

  #1651962

  Wow, nice one Mark, got it working now.