Forum Discussion

Leul2020's avatar
Leul2020
Copper Contributor
Mar 03, 2020

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

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's avatar
    Leul2020
    Copper Contributor

    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

Resources