concating strings

• sukhoi971

  SSCarpal Tunnel

  Points: 4833

  More actions

  May 20, 2004 at 4:40 am

  #108182

  WHAT I WANT TO DO IS CONCAT THE CONTENTS OF THESE INDIVIDUAL VARIABLES AND STORE IT IN A DIFFERENT VARIABLE BUT ITS NOT HAPPENING

  i have a code like this

  --------------

  set @msgContent= 'Complaint Code ' + @ComplaintCode + 'Invoice No.' + @invoiceno + 'Batch No.' + @Batchno

  --- ERROR MESSAGE

  Error converting data type varchar to numeric.

  <a href="http://www.websolsoftware.com"> For IT jobs click here</a>

  *Sukhoi*[font="Arial Narrow"][/font]

• Jeff Moden

  SSC Guru

  Points: 1004433

  More actions

  May 20, 2004 at 5:24 am

  #507094

  Sukhoi,

  It's funny how the error message is sometimes the opposite of what it should be...

  Your problem is that the contents of one or more of your variables are numeric and need to be converted to VarChar.  Depending on your server settings, you may also have to be careful about NULLS.  The default server setting say that when you concatenate nulls with anything else, the answer will be NULL.  I don't recommend changing that server setting because you could really mess up JOIN conditions.

  If all of your variables are numeric and guaranteed to never be NULL, try this...

  set @msgContent= 'Complaint Code ' + CONVERT(VARCHAR(10),@ComplaintCode)

                 + '  Invoice No. '  + CONVERT(VARCHAR(10),@invoiceno)

                 + '  Batch No. '    + CONVERT(VARCHAR(10),@Batchno)

  Notice I added a couple of spaces to your string literals to provide a little output spacing.

  If all of the variables could contain NULLs, the statment gets a little longer...

  set @msgContent= 'Complaint Code ' + CONVERT(VARCHAR(10),ISNULL(@ComplaintCode,'-None-'))

                 + '  Invoice No.'   + CONVERT(VARCHAR(10),ISNULL(@invoiceno,'-None-')) 

                 + '  Batch No.'     + CONVERT(VARCHAR(10),ISNULL(@Batchno,'-None-'))

  Of course, you may have to change VARCHAR(10) to a different length but 10 works for INT and most other integer datatypes (so would 9).  You may also want to change '-None-' to something else (maybe 'NULL' or '     ') to represent NULLs in your output.

  One other thing... you may have to read your own code in the future... make it easy on yourself... instead of putting all that code in a single long line (which will wrap or truncate during printing), do a little formatting like I did in my example.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)