can you use a variable for a field name? 1

[numbered]

I've spent about 12 hours trying to figure this out.

I'm trying to replace the asci for french characters to what it actually is.

After the database entry is made, if there are any french characters entered, I need to replace them. I'd like to do this with a loop if I can as I need to check more then one field and more then one type of french character.

declare @SQL varchar(1000)
declare @fieldName varchar(50)
declare @frenchCode varchar(50)
declare @frenchChar varchar(5)
declare @userId numeric

set @fieldName = 'firstName'
set @frenchCode = '''é'''
set @frenchChar = '''é'''
set @userId = 37
set @SQL = 'UPDATE tbl_table SET ' + @fieldName + '= replace(' + @fieldName + ', ' + @frenchCode + ', ' + @frenchChar + ') WHERE id = ' + @userId

if exists (SELECT @fieldName FROM tbl_table WHERE @fieldName like '%@frenchCode%')
exec(@SQL)

Can someone please offer some advice?

Thank you,
Seven

 

[zhavic]

At first, you don't need to check if exists some rows with french characters by EXISTS function.
You can add this check to WHERE condition of UPDATE statement:

set @SQL = 'UPDATE tbl_table SET ' + @fieldName + '= replace(' + @fieldName + ', ' + @frenchCode + ', ' + @frenchChar + ') WHERE id = ' + @userId + ' AND ' + @fieldName + ' like ''%' + @frenchCode + '%'''

Also instead of LIKE you can use CHARINDEX ( if column is not text type )
... ' AND CHARINDEX( ''' + @frenchCode + ''', ' + @fieldName + ' ) > 0'

But, if you want to use EXEISTS function, there is example, how to do it:

declare @SQL varchar(1000)
declare @fieldName varchar(50)
declare @frenchCode varchar(50)
declare @frenchChar varchar(5)
declare @userId numeric

set @fieldName = 'firstName'
set @frenchCode = '''é'''
set @frenchChar = '''é'''
set @userId = 37
set @SQL = 'UPDATE tbl_table SET ' + @fieldName + '= replace(' + @fieldName + ', ' + @frenchCode + ', ' + @frenchChar + ') WHERE id = ' + @userId


DECLARE @exists bit
DECLARE @sql_1 nvarchar(1000)

SET @exists = 0
SET @sql_1 = 'if exists (SELECT ' + @fieldName + ' FROM tbl_table WHERE ' + @fieldName + ' like ''%' + @frenchCode + '%'' ) SET @exists = 1'

EXECUTE sp_executesql @sql_1,
N'@exists bit OUTPUT',
@exists OUTPUT

IF @exists = 1
exec(@sql)




Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[numbered]

I had to do it a different way but, your code DID help!!

thank you zhavic!