SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

-- Sample data.
-- The   Counters   table has a row for each counter to be kept, rather than a table per counter.
create table Counters ( CounterId Int Identity, Counter Int Default 0, CounterName NVarChar(64) );
insert into Counters ( CounterName ) values ( 'Shoes' ), ( 'Widgets' );
select * from Counters;

-- Build the query.
declare @Counter as Int = 0;
declare @SQL as NVarChar(500);
declare @ParameterDefinitions as NVarChar(100);
declare @TableName as SysName = N'Counters';
declare @CounterName as NVarChar(64) = 'Widgets';

-- Note that the table name needs to be substituted into the query here, not passed as a parameter.
-- Using different names for the parameters within the query and the variables being passed makes
--   things a little clearer.
set @SQL =
  N'update ' + QuoteName( @TableName ) +
    ' set @pCounter = Counter += 1' +
    ' where CounterName = @pCounterName;';
set @ParameterDefinitions = N'@pCounterName NVarChar(64), @pCounter Int Output';

-- Execute the query.
-- Note that   output   parameters must be specified here as well as in the parameter definitions.
execute sp_ExecuteSQL @SQL, @ParameterDefinitions,
  @pCounterName = @CounterName, @pCounter = @Counter output;
select @CounterName as [@CounterName], @Counter as [@Counter];
select * from Counters;

execute sp_ExecuteSQL @SQL, @ParameterDefinitions,
  @pCounterName = @CounterName, @pCounter = @Counter output;
select @CounterName as [@CounterName], @Counter as [@Counter];
select * from Counters;

-- Try a different counter.
set @CounterName = N'Shoes';
execute sp_ExecuteSQL @SQL, @ParameterDefinitions,
  @pCounterName = @CounterName, @pCounter = @Counter output;
select @CounterName as [@CounterName], @Counter as [@Counter];
select * from Counters;

-- Houseclean.
drop table Counters;

CounterId	Counter	CounterName
1	0	Shoes
2	0	Widgets

@CounterName	@Counter
Widgets	1

CounterId	Counter	CounterName
1	0	Shoes
2	1	Widgets

@CounterName	@Counter
Widgets	2

CounterId	Counter	CounterName
1	0	Shoes
2	2	Widgets

@CounterName	@Counter
Shoes	1

CounterId	Counter	CounterName
1	1	Shoes
2	2	Widgets