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
