We help IT Professionals succeed at work.
Get Started

Dynamic SQL Error

ScuzzyJo asked
Last Modified: 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;'
      EXEC (@sqlstr);

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.

Watch Question
IT Engineer
Distinguished Expert 2017
This problem has been solved!
Unlock 2 Answers and 4 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE