Hi I am trying to run turn this update query into a tool where I can just define table and column names in a variable.
Declare @ExecQuery as nvarchar(max)
Declare @tableName as nvarchar(50)
Declare @Colfrom as nvarchar(50)
Declare @Colto as nvarchar(50)
Set @TableName = 'upd_employees'
Set @ColFrom = 'EmpCode_Edit'
Set @ColTo = 'EmpName'
--Set @ExecQuery = 'Select '+@Colfrom+','+@Colto+' from '+@TABLENAME
Set @ExecQuery = 'update '+@TABLENAME+' set '+@Colto+' = CASE CHARINDEX('-','+@Colfrom+')
WHEN 0 THEN EmpCode_Edit
ELSE
CASE CHARINDEX('-',SUBSTRING('+@Colfrom+',CHARINDEX('-','+@Colfrom+')+1,LEN('+@Colfrom+')-CHARINDEX('-','+@Colfrom+')+1))
WHEN 0 THEN SUBSTRING('+@Colfrom+',1,(CHARINDEX('-','+@Colfrom+')-1))
ELSE SUBSTRING('+@Colfrom+',1,
CHARINDEX('-','+@Colfrom+')+
CHARINDEX('-',SUBSTRING('+@Colfrom+',CHARINDEX('-','+@Colfrom+')+1,LEN('+@Colfrom+')-CHARINDEX('-','+@Colfrom+')+1))-1)
END
END'
EXEC sp_executesql @ExecQuery
When I run the query I get this error:
Msg 402, Level 16, State 1, Line 12 The data types nvarchar and varchar are incompatible in the subtract operator.
I know that you cannot directly save table and column names as variables but when I use the same method Set @ExecQuery as a string and then running it using EXEC sp_executesql @ExecQuery it works with simple queries (see comment --Set @ExecQuery = 'Select '+@Colfrom+','+@Colto+' from '+@TABLENAME) . Why is it not working when I use it in this CASE statement?
1 Answer
Looking at this line:
Set @ExecQuery = 'update '+@TABLENAME+' set '+@Colto+' = CASE CHARINDEX('-','+@Colfrom+')
You have a literal - right between your concatenation statements, not inside your VARCHAR. You need to double single quotes if you want to build Dynamic SQL that contains single quotes.
It should become:
Set @ExecQuery = 'update '+@TABLENAME+' set '+@Colto+' = CASE CHARINDEX(''-'','+@Colfrom+')
Apply this way of working to the rest of your query.
Remarks:
- Generally you are best to quote table names and column names using QUOTENAME. That is to allow table names and column names with spaces and other special characters.
Set @ExecQuery = 'update '+QUOTENAME(@TABLENAME)+' set '+QUOTENAME(@Colto)+' = CASE CHARINDEX(''-'','+QUOTENAME(@Colfrom)+')
- You are possibly leaving yourself open to SQL Injection attacks if you concatenate strings in a Dynamic SQL statement that way. You are best to replace single quotes with two single quotes in the strings you append:
SET @TABLENAME=REPLACE(@TABLENAME,'''','''''');
SET @Colto=REPLACE(@Colto,'''','''''');
