Dynamic SQL Error
Posted on 2016-09-16
I'm pretty new to using dynamic SQL and have the following bit of code:
SET @maxcounter = 0;
WHILE @maxcounter < @maxcnt
SET @crtcnt = @maxcounter + 1;
SET @maxcounter = @maxcounter + 1;
SET @sqlstr = 'UPDATE LFRD SET old_ssn' + @crtcnt + ' = substring(ConcatF,13*' + @maxcounter + ',13) WHERE old_ssn Is Not Null OR @maxcounter < SSNCount;'
The variables are declared and used earlier, so I'm pretty sure that isn't the problem.
I'm getting the error message:
Msg 245, Level 16, State 1, Line 137
Conversion failed when converting the nvarchar value 'UPDATE LFRD SET old_ssn1 = substring(ConcatF,13*' to data type int.
Which refers to the @sqlstr = line.
I'm pretty sure it's my syntax, but I don't understand why it's trying to convert to a data type int.
The line is supposed to fill various columns (the number is dynamic) with data from a concatenated column. Each block of data is 13 characters long. I'm trying to say that if @maxcounter is 1, start at character 13 (probably needs to be 14, but I can deal with that later) and pick up 13 characters. If @maxcounter is 2, start at character 26 (or maybe 27) and pick up 13 characters, and so on.
Can anybody please help me here? I don't know where I'm going wrong.