CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Rehana Sultana
Rehana Sultana

Reputation: 49

How do I update a specific column value for all tables in a database

create procedure change_ardivisionno
@CustomerNo nvarchar(7),@table_name varchar(40)

AS
Begin
SET NOCOUNT ON
Update @table_name
SET ARDivisionNo = '21'
where @CustomerNo = (select customerno from mas_abc.dbo.customerlist)
and @table_name = (select tablename from table_list)
END

I'm trying to update ARDisivionNo (ARDivisionno is the column name) value set to 21 in all tables of the database where customerno is the list of customers stored in customer list table.

I'm trying to create this procedure but I keep getting the error:

*Must declare the table variable "@table_name".*

Upvotes: 2

Views: 4305

Answers (2)

Rawheiser
Rawheiser

Reputation: 1218

You need to dynamically create the SQL to execute, as the tableName can't be a String variable.

See sp_executesql.

declare @DBName varchar(40); 
set @DBName = 'AdventureWorks2012';
EXECUTE sp_executesql 
          N'SELECT * FROM '+@DBName+'.HumanResources.Employee 
          WHERE BusinessEntityID = @level',
          N'@level tinyint',
          @level = 109;

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280260

Something like this, except I don't understand how your current WHERE clause is supposed to limit the update to a set of rows in the target table. Right now it seems like it will update the entire table; I suspect there is a join condition missing. 

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'UPDATE t
  SET ARDivisionNo = ''21''
  FROM dbo.' + QUOTENAME(tablename) + ' AS t
  INNER JOIN mas_abc.dbo.customerlist AS c
  ON c.customerno = @CustomerNo;';

PRINT @sql;
--EXEC sp_executesql @sql, N'@Cust NVARCHAR(7)', @CustomerNo;

Upvotes: 2

Related Questions