2 Replies - 1859 Views - Last Post: 08 February 2011 - 08:51 AM

#1 hoffmn   User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 46
  • Joined: 22-October 09

Increment a variable after a string

Posted 06 January 2011 - 09:02 AM

I have a process to create student AD and Exchange accounts. We have three mail stores. I need to balance between the three mail stores so it does not overload one and corrupt data. I thought I would use RAND somehow to generate a random number 1-3. I would then append this number to the string to tell it which mailstore to assign the student account to. However when I use the output, it only uses the one number it comes up with for all the records. I need to figure out a way to randomize or increment 1-3 so I can put that number onto the end of my string.

I have a feeling I am doing this wrong, but I wanted to try something before asking. Here is the code that creates the random number.

---- Create the variables for the random number generation
DECLARE @Random varchar;
DECLARE @Upper int;
DECLARE @Lower int

---- This will create a random number between 1 and 3
SET @Lower = 1 ---- The lowest random number
SET @Upper = 3 ---- The highest random number
SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
SELECT @Random

This is how I call it later when selecting it into the table.

'StudStore' + @Random AS exchangeStore

As a side note, I have seen some other ideas floating around the net that say to use identity or sequences, but I am still new to TSQL and need some assistance. I know everyone on here is extremely busy with other things, I appreciate any help I can get.

Is This A Good Question/Topic? 0
  • +

Replies To: Increment a variable after a string

#2 ForcedSterilizationsForAll   User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: Increment a variable after a string

Posted 04 February 2011 - 11:03 AM

You could always do a count of all three tables and put it in whichever table has the least amount of records. I'd think with properly indexed tables that they'd never get too large so performance wouldn't really be an issue. Just put the counts each in their own variable and you're set. With the random numbers there's still a chance of one table having quite a bit more than other.
Was This Post Helpful? 0
  • +
  • -

#3 hoffmn   User is offline

  • New D.I.C Head

Reputation: -1
  • View blog
  • Posts: 46
  • Joined: 22-October 09

Re: Increment a variable after a string

Posted 08 February 2011 - 08:51 AM

View PostForcedSterilizationsForAll, on 04 February 2011 - 11:03 AM, said:

You could always do a count of all three tables and put it in whichever table has the least amount of records. I'd think with properly indexed tables that they'd never get too large so performance wouldn't really be an issue. Just put the counts each in their own variable and you're set. With the random numbers there's still a chance of one table having quite a bit more than other.


Thanks for the reply, but, I actually solved this on my own. I used the studentUID since that is a unique number to each student then I did some math on that number. It works quite well and is as close to random as I could get this.

'StudStore' 
+ CONVERT(varchar, ROUND(((CSV.StudentUID % 3) + 1),0),0) AS ExchangeStore 


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1