title: SQL Prime number function
body: <p>If I have a number X and want to say <code>IsPrime(X) = true/false</code> using sql-server what is the best approach?</p>
  
  <p>Do I just import a table of primes or is there an algorithm that is fairly efficient for the smaller primes?</p>
  
  <p>Note: I'm not interested in numbers greater than approx. 10 million.</p>
  
  <p>Ended up using the following:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[isPrime]
  (
      @number INT
  )
  RETURNS VARCHAR(10)
  
  BEGIN
  
  
      DECLARE @retVal VARCHAR(10) = 'TRUE';
  
      DECLARE @x INT = 1;
      DECLARE @y INT = 0;
  
      WHILE (@x &lt;= @number )
      BEGIN
  
              IF (( @number % @x) = 0 )
              BEGIN
                  SET @y = @y + 1;
              END
  
              IF (@y &gt; 2 )
              BEGIN
                  SET @retVal = 'FALSE'
                  BREAK
              END
  
              SET @x = @x + 1
  
      END
  
      RETURN @retVal
  END
  </code></pre>