-1
alter procedure SP_AUTO
@table_name varchar(200),
@careof_check varchar(200),
@ACCTG_careof_check varchar(200)
AS

begin
declare @code_assgn varchar(2000)

set @code_assgn='
if COL_LENGTH('+@table_name+','+@careof_check+')is null
begin
alter table '+@table_name+'
add '+@careof_check+' varchar(200)
print ''careof column added''
end
if COL_LENGTH('+@table_name+','+@ACCTG_careof_check+') is null
begin
alter table '+@table_name+'
add '+@ACCTG_careof_check+' varchar(200)
print ''ACCTG_CAREOF column added''
end
else
begin
print ''BOTH COLUMN ARE ALREADY PRESENT''
end'

print(@code_assgn)
exec(@code_assgn)
end


exec sp_auto 'auto','careof','acctg_careof'
3
  • 2
    Can you post the full error message and the result of the print? Will be pretty clear to see, it doesn't seem a complex dynamic build.
    – EzLo
    Commented Mar 19, 2019 at 9:19
  • 1
    Try changing the COL_LENGTH() functions to if COL_LENGTH('''+@table_name+''','''+@careof_check+''')is null & if COL_LENGTH('''+@table_name+''','''+@ACCTG_careof_check+''') is null Commented Mar 19, 2019 at 9:35
  • You should also add the schema to the 'auto' table in the procedure call. Commented Mar 19, 2019 at 11:13

1 Answer 1

3

In the dynamic SQL query, to escape the string value you need to add two single quotes.

Please find the following block for construct the dynamic query:

declare @code_assgn nvarchar(2000) = '';

set @code_assgn = '
if COL_LENGTH(''' + @table_name + ''', ''' + @careof_check + ''') is null
begin
    alter table ' + @table_name + '
    add ' + @careof_check + ' varchar(200);  
    print ''careof column added''
end
if COL_LENGTH(''' + @table_name + ''', ''' + @ACCTG_careof_check + ''') is null
begin
    alter table ' + @table_name + '
    add ' + @ACCTG_careof_check + ' varchar(200);  
    print ''ACCTG_CAREOF column added''
end
else
begin
    print ''BOTH COLUMN ARE ALREADY PRESENT''
end'

Not the answer you're looking for? Browse other questions tagged or ask your own question.