-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'

closed as off-topic by mustaccio, Marco, LowlyDBA, John Eisbrener, John aka hot2use Mar 20 at 10:21

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, Marco, LowlyDBA, John Eisbrener, John aka hot2use
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 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 Mar 19 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 – Randi Vertongen Mar 19 at 9:35
  • You should also add the schema to the 'auto' table in the procedure call. – Randi Vertongen Mar 19 at 11:13
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.