title: SQL User Defined Function with Random Numbers
body: <p>I hope someone can help me.</p>
  
  <p>I an trying to use an SQL user defined function on MS server 2008 to do the following</p>
  
  <p>I am trying to predict when a customer will leave the bank, and I am doing this with series of coin-tosses until you leave. The coin-tosses do not necessarily are always at 50/50, so I can pass the probability to the function as a parameter as well as the current customer tenure.</p>
  
  <p>The problem is that it is not allowed to use RAND() inside the function, I do not understand why but that is fine,there must be a reason.</p>
  
  <p>here is my function:</p>
  
  <pre><code>create function NewTenure (@tenure integer, @p float) 
  returns integer
  as
  begin
      declare @r float
      declare @newten int
      declare @attrite binary
      set @attrite=0
      set @newten = @tenure
      set @r = RAND()
      while (@attrite = 0)
      begin
          if (@r &lt;= @p) set @attrite = 1;
          if (@r &gt; @p) set @newten = @newten+1    
      end
      return(@newten)
  end
  </code></pre>
  
  <p>As I see it, I need to generate a random value between 0-1 so I can compare it to the probability to determine if the customer left or not. If RAND() is not allowed, is there an SQL trick to generate it inside the function? (I know from school many years ago that I can write my own random number generator, but I do not want to do that, nor I believe I should have to do that, a random number is a basic computer function that one should expect - any other language I have ever used had one)</p>
  
  <p>I plan to the call the function in a new query to update a temp table with the predicted tenure when they leave, which will then be used for other complex calculations.</p>