Update table with auto increment using sp_executesql

Question

I'm trying to translate this simple sql statement into sp_executesql, because the table name is variable:

UPDATE @TableName SET @i = RowNumber = @i + 1

This is what I came up with, but it gives an error upon execution : "Must declare the table variable "@TableName".

DECLARE @i AS INT ;
SET @i = 0 ;
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);

SET @SQLString = N'UPDATE @TableName SET @i = RowNumber = @i + 1';
SET @ParmDefinition = N'@TableName VARCHAR(30), @i  TINYINT';

EXECUTE sp_executesql @SQLString, @ParmDefinition, @TableName=@TableName, @i = @i;

I'm trying to add another column to an existing table, that is basically like an identity column (incremented ID). Tables already have an Id column and I can't drop/update it.

Please don't recommend identity column.

I'm executing this query inside a stored procedure, and @TableName comes in as a parameter and is the name of a temp table, so I was expecting it to be available inside all the inner scopes (including the sp_exec).

Answer 1

First of all, table name cannot be parametrized. I propose to provide it as string literal secured with QUOTENAME instead. If both schema and tablename are provided, they have to be provided as separate parameters and both quoted.

Second: SET @i = RowNumber = @i + 1' looks a way to iteratively change RowNumber with consecutive integers. For that I would use updatetable cte with ROW_NUMBER and add starting position(here @i);

Third: It is assumed that column RowNumber already exists in table. If not dynamic ALTER TABLE <TableName> ADD RowNumber INT have to be issued first.

Full code:

DECLARE @TableName SYSNAME = 't';

DECLARE @i AS INT = 0 ;
DECLARE @SQLString NVARCHAR(MAX);
DECLARE @ParmDefinition NVARCHAR(MAX);

SET @SQLString = 
N'WITH cte AS (SELECT *, rn = ROW_NUMBER() OVER(ORDER BY (SELECT 1)) FROM <TableName>)
  UPDATE cte SET RowNumber = @i + rn';
SET @ParmDefinition = N'@i  TINYINT';

SET @SQLString = REPLACE(@SQLString, '<TableName>', QUOTENAME(@TableName));

PRINT @SQLString;  -- debug

EXECUTE sp_executesql @SQLString, @ParmDefinition, @i = @i;

db<>fiddle demo

Answer 2

Use CONCAT function instead of + Operator

DECLARE @SQL NVARCHAR(MAX)=NULL

DECLARE @TABLE_NAME NVARCHAR(MAX)='Employee',@Col_NAME NVARCHAR(MAX)='Rw'

SET @SQL=CONCAT('SELECT *,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as ID FROM ',QUOTENAME(@TABLE_NAME))

SELECT @SQL

EXEC (@SQL)

Update Table Column

By using Nested Sub - Query you can update the Column of the table

SET @SQL=CONCAT('UPDATE X',' SET X.',@Col_NAME,' = X.ID FROM (SELECT ',@Col_NAME,', ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) as ID FROM ',QUOTENAME(@TABLE_NAME),' ) X')

SELECT @SQL

EXEC (@SQL)

Answer 3

The following code demonstrates how to handle the various parameters and return a value from the dynamic SQL.

Rather than using a table-per-counter, the code uses a single table of counters with a name (CounterName) assigned to each. Incrementing the different counters within the table is shown.

Instead of passing in a value to be incremented, e.g. the OP's @i, the counters maintain their own values and the incremented value is returned on each execution. If passing in the value to be incremented is a requirement then the update can be easily changed to use the value of @pCounter as a source and the value can be passed in and returned using one parameter.

The table name is embedded in the update statement and, as such, cannot be passed as a parameter. It must be inserted into the statement while it is being assembled. QuoteName is used to handle problem names, e.g. Space Here, and offer some protection against SQL injection.

The code can be tested at db<>fiddle. (At the time of this writing SQL Server 2019 appears to be non-functional at db<>fiddle. Hence the use of SQL Server 2017.)

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

Answer 4

When you put variables inside quotes it don't been evaluated, just text. Also it's better to explicit write column name that you want to update.

DECLARE @i AS INT ; 
SET @i = 0 ;
DECLARE @ColumnName as NVARCHAR(1000) ='MyColumn'
DECLARE @TableName as NVARCHAR(1000) = 'MyTable'
DECLARE @SQLString NVARCHAR(500);

SET @SQLString = N'UPDATE ' +  @TableName + ' SET ' + @ColumnName  + ' = ' + @i + '+1';

EXECUTE sp_executesql @SQLString;

Conclusion - you should concatenate whole update string in single variable and then run it by sp_executesql