Dynamic Column add to table from other table columns and getting error what wrong i'm doing here

%3CLINGO-SUB%20id%3D%22lingo-sub-1207800%22%20slang%3D%22en-US%22%3EDynamic%20Column%20add%20to%20table%20from%20other%20table%20columns%20and%20getting%20error%20what%20wrong%20i'm%20doing%20here%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1207800%22%20slang%3D%22en-US%22%3E%3CP%3Ealter%20PROCEDURE%20%5Bdbo%5D.%5BINSERT_CERTIFICATE_FIELD%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAS%3C%2FP%3E%3CP%3Edeclare%20%40ColumnName%20varchar(100)%3C%2FP%3E%3CP%3Edeclare%20%40field_List%20varchar(100)%3C%2FP%3E%3CP%3Edeclare%20%40getColumnName%20cursor%3C%2FP%3E%3CP%3Edeclare%20%40SQLFieldUpdate%20varchar(4000)%3C%2FP%3E%3CP%3Edeclare%20%40tableName%20varchar(100)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eset%20%40tableName%3D'CERTIFICATE_DATA'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E---------------------------------------------------------------------------------%3C%2FP%3E%3CP%3E--get%20all%20CERTIFICATE_FIELD_NAME%20list%20from%20CERTIFICATE_FIELD%20add%20check%20all%20are%20created%20as%20field%20name%20to%20CERTIFICATE_DATA%20table%3C%2FP%3E%3CP%3E--%20TO%20CHECK%20ALL%20FIELD%20CREATED%20HERE%20EXIST%20IN%20CERTIFICATE_DATA%20AS%20COLUMN%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20set%20%40getColumnName%3D%20cursor%20for%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20select%20FIELD_NAME%20from%26nbsp%3B%20CERTIFICATE_FIELD%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20open%20%40GetColumnName%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20fetch%20next%20from%26nbsp%3B%20%40getcolumnName%20into%20%40columnName%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20while%20%40%40FETCH_STATUS%3D0%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Begin%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20SET%20%40SQLFieldUpdate%3D'IF%20COL_LENGTH('%2B%40tableName%2B'%2C%20'%2B%40columnName%2B')%20IS%26nbsp%3B%20NULL%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20BEGIN%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3Balter%20table%20'%2B%40tableName%2B'%20add%26nbsp%3B%20'%26nbsp%3B%26nbsp%3B%20%2B%20%40columnName%20%2B%26nbsp%3B%20'%20varchar(300)%20null%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20END'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20%26nbsp%3B%20exec%20(%40SQLFieldUpdate)%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20fetch%20next%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20from%20%40GetColumnName%20into%20%40columnName%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20end%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20Close%20%40GetColumnName%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20deallocate%20%40GetColumnName%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20--exec%20INSERT_CERTIFICATE_FIELD%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eerror%20message%3C%2FP%3E%3CP%3EMsg%20207%2C%20Level%2016%2C%20State%201%2C%20Line%2049%3C%2FP%3E%3CP%3EInvalid%20column%20name%20'CERTIFICATE_DATA'.%3C%2FP%3E%3CP%3EMsg%20207%2C%20Level%2016%2C%20State%201%2C%20Line%2049%3C%2FP%3E%3CP%3EInvalid%20column%20name%20'FIELD1'.%3C%2FP%3E%3CP%3EMsg%20207%2C%20Level%2016%2C%20State%201%2C%20Line%2049%3C%2FP%3E%3CP%3EInvalid%20column%20name%20'CERTIFICATE_DATA'.%3C%2FP%3E%3CP%3EMsg%20207%2C%20Level%2016%2C%20State%201%2C%20Line%2049%3C%2FP%3E%3CP%3EInvalid%20column%20name%20'FIELD2'.%3C%2FP%3E%3CP%3EMsg%20207%2C%20Level%2016%2C%20State%201%2C%20Line%2049%3C%2FP%3E%3CP%3EInvalid%20column%20name%20'CERTIFICATE_DATA'.%3C%2FP%3E%3CP%3EMsg%20207%2C%20Level%2016%2C%20State%201%2C%20Line%2049%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1207832%22%20slang%3D%22en-US%22%3ERe%3A%20Dynamic%20Column%20add%20to%20table%20from%20other%20table%20columns%20and%20getting%20error%20what%20wrong%20i'm%20doing%20here%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1207832%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F573712%22%20target%3D%22_blank%22%3E%40Leul2020%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20field%20names%20are%20under%20%22%26nbsp%3B%3CSPAN%3EFIELD_NAME%22%20in%20table%26nbsp%3B%20%22CERTIFICATE_FIELD%22%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eselect%20FIELD_NAME%20from%26nbsp%3B%20CERTIFICATE_FIELD%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Ewe%20have%20anther%26nbsp%3Btable%20'CERTIFICATE_DATA'%20where%20the%26nbsp%3B%20new%20columns%20is%20created%20from%20CERTIFICATE_FIELD%20If%20NOT%20EXISTS%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Ethe%20error%20i%20get%20from%20the%20store_procedure%20is%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eerror%20message%3C%2FP%3E%3CP%3EMsg%20207%2C%20Level%2016%2C%20State%201%2C%20Line%2049%3C%2FP%3E%3CP%3EInvalid%20column%20name%20'CERTIFICATE_DATA'.%3C%2FP%3E%3CP%3EMsg%20207%2C%20Level%2016%2C%20State%201%2C%20Line%2049%3C%2FP%3E%3CP%3EInvalid%20column%20name%20'FIELD1'.%3C%2FP%3E%3CP%3EMsg%20207%2C%20Level%2016%2C%20State%201%2C%20Line%2049%3C%2FP%3E%3CP%3EInvalid%20column%20name%20'CERTIFICATE_DATA'.%3C%2FP%3E%3CP%3EMsg%20207%2C%20Level%2016%2C%20State%201%2C%20Line%2049%3C%2FP%3E%3CP%3EInvalid%20column%20name%20'FIELD2'.%3C%2FP%3E%3CP%3EMsg%20207%2C%20Level%2016%2C%20State%201%2C%20Line%2049%3C%2FP%3E%3CP%3EInvalid%20column%20name%20'CERTIFICATE_DATA'.%3C%2FP%3E%3CP%3EMsg%20207%2C%20Level%2016%2C%20State%201%2C%20Line%2049%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

alter PROCEDURE [dbo].[INSERT_CERTIFICATE_FIELD]

 

AS

declare @ColumnName varchar(100)

declare @field_List varchar(100)

declare @getColumnName cursor

declare @SQLFieldUpdate varchar(4000)

declare @tableName varchar(100)

 

set @tableName='CERTIFICATE_DATA'

 

---------------------------------------------------------------------------------

--get all CERTIFICATE_FIELD_NAME list from CERTIFICATE_FIELD add check all are created as field name to CERTIFICATE_DATA table

-- TO CHECK ALL FIELD CREATED HERE EXIST IN CERTIFICATE_DATA AS COLUMN

 

       set @getColumnName= cursor for

       select FIELD_NAME from  CERTIFICATE_FIELD

 

       open @GetColumnName

       fetch next from  @getcolumnName into @columnName

              while @@FETCH_STATUS=0

                     Begin

                           SET @SQLFieldUpdate='IF COL_LENGTH('+@tableName+', '+@columnName+') IS  NULL

                                                       BEGIN

                                                                      alter table '+@tableName+' add  '   + @columnName +  ' varchar(300) null

                                                              END'

 

                       exec (@SQLFieldUpdate)

                    

                     fetch next

                     from @GetColumnName into @columnName

 

              end

       Close @GetColumnName

       deallocate @GetColumnName

 

 

       --exec INSERT_CERTIFICATE_FIELD

 

 

error message

Msg 207, Level 16, State 1, Line 49

Invalid column name 'CERTIFICATE_DATA'.

Msg 207, Level 16, State 1, Line 49

Invalid column name 'FIELD1'.

Msg 207, Level 16, State 1, Line 49

Invalid column name 'CERTIFICATE_DATA'.

Msg 207, Level 16, State 1, Line 49

Invalid column name 'FIELD2'.

Msg 207, Level 16, State 1, Line 49

Invalid column name 'CERTIFICATE_DATA'.

Msg 207, Level 16, State 1, Line 49

1 Reply

@Leul2020 

 

the field names are under " FIELD_NAME" in table  "CERTIFICATE_FIELD"

select FIELD_NAME from  CERTIFICATE_FIELD

 

we have anther table 'CERTIFICATE_DATA' where the  new columns is created from CERTIFICATE_FIELD If NOT EXISTS

 

the error i get from the store_procedure is

error message

Msg 207, Level 16, State 1, Line 49

Invalid column name 'CERTIFICATE_DATA'.

Msg 207, Level 16, State 1, Line 49

Invalid column name 'FIELD1'.

Msg 207, Level 16, State 1, Line 49

Invalid column name 'CERTIFICATE_DATA'.

Msg 207, Level 16, State 1, Line 49

Invalid column name 'FIELD2'.

Msg 207, Level 16, State 1, Line 49

Invalid column name 'CERTIFICATE_DATA'.

Msg 207, Level 16, State 1, Line 49