sql server - hard time trying to discover whats wrong with a sp_executesql with params -

i'm trying create stored procedure drop triggers follow name schema basead on table name (after create trigger).

so, first, tried use cursor list tables ends __attach, check if table has trigger name starts update_att_name__, , if exists, drop trigger.

my sp:

declare @table_name nvarchar(max); declare @trigger_name nvarchar(max); declare tables_cursor cursor   select table_name   information_schema.tables   table_type = 'base table' , table_name '%attach'   read only;  open tables_cursor  fetch next tables_cursor @table_name  while @@fetch_status = 0   begin     /* check if trigger exists */     set @trigger_name = concat('update_att_name__', @table_name);     if (exists(select * sys.triggers object_id = object_id(@trigger_name)))       /* drop trigger */       begin         declare @sql nvarchar(max);         declare @param nvarchar(max);          set @sql = n'drop trigger @tn';         set @param = n'@tn nvarchar(max)';          execute sp_executesql @sql, @param, @tn = @trigger_name;       end      fetch next tables_cursor @table_name   end close tables_cursor; deallocate tables_cursor; 

but receive (my sql server it's not english, so, don't known correct error message on english - have translated):

incorrect syntax found during sp_executesql next '@tn'

using print replace instead of execute sp_executesql (poor man debug), don't see wrong.

print replace(@sql, '@tn', @trigger_name)

got drop trigger update_att_name__test_table_name__attach

update, got working using

set @sql = n'drop trigger ' + @trigger_name; execute sp_executesql @sql; 

but don't how use params

why not straight forward instead? here leveraging system objects generate our dynamic sql.

declare @sql nvarchar(max) = ''  select @sql = @sql + 'drop trigger ' + t. name + ';' sys.triggers t join sys.objects o on t.parent_id = o.object_id o.name '%attach'     , t.name 'update_att_name__%'  select @sql --uncomment following line when satisfied dynamic sql generated correct --exec sp_executesql @sql