Concatenating negative number

• quongo79

  Grasshopper

  Points: 16

  More actions

  November 4, 2011 at 9:51 am

  #259546

  I have a stored procedure that needs to return the next number in the sequence a particular sequence. The first part of the sequence is a series of numbers (i.e. '20110001'). The second part of the sequence will be something like '-1' or '-8'.... some negative number. So the stored procedure should return something like this '20110001-1' or '20110001-2'

  Below is a stripped down version of my stored proc, but should be able to get the idea. The error I'm getting is: Conversion failed when converting the varchar value '20110001-11' to data type int.

  DECLARE @VAL1 varchar(15);

  DECLARE @VAL2 VARCHAR(15);

  DECLARE @VAL3 VARCHAR(15);

  SET @VAL1 = '20110001';

  SET @VAL2 = '-11';

  SET @VAL3 = CAST(@VAL1 AS VARCHAR(15)) + CAST(@VAL2 AS VARCHAR(15));

  RETURN @VAL3;

  I've tried CAST, STR, and CONVERT, but I can't get this to work.

  Thanks!

  -Q

• GSquared

  SSC Guru

  Points: 260824

  More actions

  November 4, 2011 at 9:58 am

  #1403357

  The problem isn't the concatenation. It's the use of "RETURN".

  The Return operator can only return an integer value. It's supposed to be an error code or similar item.

  That's not true of Return in scalar UDFs, but that's not how you have it scripted here.

  Change that to Select and you'll get the string you built.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• quongo79

  Grasshopper

  Points: 16

  More actions

  November 4, 2011 at 10:13 am

  #1403370

  GSquared,

  You rock! that worked like a charm!

  Thanks again,

  -Q

• Jeff Moden

  SSC Guru

  Points: 1004716

  More actions

  November 4, 2011 at 5:14 pm

  #1403543

  quongo79 (11/4/2011)

  ---

  I have a stored procedure that needs to return the next number in the sequence a particular sequence. The first part of the sequence is a series of numbers (i.e. '20110001'). The second part of the sequence will be something like '-1' or '-8'.... some negative number. So the stored procedure should return something like this '20110001-1' or '20110001-2'

  Below is a stripped down version of my stored proc, but should be able to get the idea. The error I'm getting is: Conversion failed when converting the varchar value '20110001-11' to data type int.

  DECLARE @VAL1 varchar(15);

  DECLARE @VAL2 VARCHAR(15);

  DECLARE @VAL3 VARCHAR(15);

  SET @VAL1 = '20110001';

  SET @VAL2 = '-11';

  SET @VAL3 = CAST(@VAL1 AS VARCHAR(15)) + CAST(@VAL2 AS VARCHAR(15));

  RETURN @VAL3;

  I've tried CAST, STR, and CONVERT, but I can't get this to work.

  Thanks!

  -Q

  Now that you have the RETURN problem solved, how are you solving the problem of preventing duplicates when more than one call is made to this code? 😉

  --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)