Update a specified field in all tables of the database. The syntax error occurs when you append a pair of 'convert varchar values to columns whose data type is 'int'. How can this problem be solved?

In some cases, you must update all tables with a specific field in the database. For example: update all data in a system belonging to Company A as belonging to Company B. If you manually change a table to a table, it will be a waste of time and mechanical repetition. In addition, if you are not familiar with the system database structure, you need to query and check whether this field exists in one table.
Recently I had such a requirement, so I made a general stored procedure to solve this problem.

The basic knowledge points of the database used in this stored procedure are:
1. How to find all the tables in the database
2. How to find all the fields in the table
3. How to execute SQL statements connected by strings (execute dynamic SQL statements)
4. Execute dynamic SQL statements and return values
5. Dynamic Cursor

Create procedure sp_fix_update_tables_appoint_column
@ Column_name nvarchar (100): Specifies the field name to be updated.
@ Value_to_reap1_varchar (255), which is updated only when it is worthwhile.
@ Value_new varchar (255), -- New Value assignment
@ Table_not_inclue varchar (255) -- excluded tables that are not updated (in some cases, a specific table may not need to be updated)
As
If ltrim (rtrim (@ table_not_inclue) =''
Set @ table_not_inclue = ''''''
Else
Set @ table_not_inclue = ''' + Replace (@ table_not_inclue, ',', ''', ''') + ''''

Declare @ table_name as nvarchar (255)
Declare @ SQL _cur as varchar (2000)
Set @ SQL _cur = 'Clare cur_tables cursor
Select name from sysobjects where xtype = ''u'' and name not in ('+ @ table_not_inclue + ')'
Exec (@ SQL _cur)
Open cur_tables
Fetch next from cur_tables into @ table_name
While @ fetch_status = 0
Begin

Declare @ column_count as int
Declare @ SQL _find_column as nvarchar (1000)
Set @ SQL _find_column = n' select @ column_count = count (DBO. syscolumns. Name)
From DBO. syscolumns inner join
DBO. sysobjects on DBO. syscolumns. ID = DBO. sysobjects. ID
Where DBO. sysobjects. Name = ''' + @ table_name + ''''
+ 'And DBO. syscolumns. Name = ''' + @ column_name + ''''
+ 'And (DBO. sysobjects. xtype = ''u'') and (not (DBO. sysobjects. name like ''dtproperties ''))'

Exec sp_executesql @ SQL _find_column, n' @ table_name varchar (233), @ column_name varchar (100), @ column_count int output'
, @ Table_name, @ column_name, @ column_count output

If (@ column_count = 1)
Begin
Print @ table_name
Declare @ SQL _update as varchar (1000)
Set @ SQL _update = 'update' + @ table_name + 'set' + @ column_name + '= ''' + @ value_new + '''where'
+ @ Column_name + '= ''' + @ value_to_reaplace + ''''
Exec (@ SQL _update)
End
Fetch next from cur_tables into @ table_name
End
Close cur_tables
Deallocate cur_tables

 

If the parameter is an integer, convert the type to varchar by using case when using the parameter. Otherwise, a syntax error occurs when the varchar value is converted to a column whose data type is int.

Set @ SQL _update = 'update' + @ table_name + 'set' + @ column_name + '= ''' + case (@ value_new as varchar) + '''where'
+ @ Column_name + '= ''' + case (@ value_to_reap;as varchar) + ''''