SQL + print common factors of two numbers

Question

I feel really dumb because I could easily do this in java or C# with a for loop but I am new to SQL and I am having trouble figuring this out. What I have so far is that I have declared two integer variables for the numbers I want to find the common factors for. However with SQL from what I am reading I need to use a while loop to cycle throuh the numbers to find the common factors, am I on the right track? After that I just want SQL server to print the common factors. Here is the code I have so far...

DECLARE @num1 int = 10;
DECLARE @num2 int = 20;
DECLARE @count INT = 1;

WHILE @count < @num1
BEGIN
IF (@num % @count = ?)
PRINT 'common factors'
END

Am I at least on the right track here? Thanks in advance for any help

SQL is designed for data stored in tables. You can use the scripting language for other purposes, but it is not nearly as powerful as a language such as C# and not well-suited to this sort of problem. — Gordon Linoff, Oct 28 '14 at 02:05
lol, sorry guys. Yes I am aggravated because its part of a my homework assignment, I breezed right through all the questions except this one. I am thinking the same thing. Why do I need to learn this in SQL is beyond me cause I agree, why would a person do this in SQL. — user3530547, Oct 28 '14 at 02:30
At least you're honest about homework. Check out this [question](http://stackoverflow.com/questions/4487546/do-while-loop-in-sql-server-2008). — programmer43229, Oct 28 '14 at 02:34

Felix Pamittan · Answer 1 · 2015-02-17T02:56:48.740

Try this one without the loop:

declare @num1 int
declare @num2 int
declare @f varchar(4000)
select @num1 = 12, @num2 = 26, @f = ''

;with lv0 as (select 0 g union all select 0)
    ,lv1 as (select 0 g from lv0 a cross join lv0 b) -- 4
    ,lv2 as (select 0 g from lv1 a cross join lv1 b) -- 16
    ,lv3 as (select 0 g from lv2 a cross join lv2 b) -- 256
    ,tally (n) as (select row_number() over (order by (select null)) from lv3)
select @f = @f + ', ' + convert(varchar(4000),n)
from tally
where 
    n <= @num1
    and @num1 % n = 0
    and @num2 % n = 0
print stuff(@f, 1, 2, '')

score 0 · Answer 2 · answered Oct 28 '14 at 22:42

Thanks guys, user3530547 links helped me figure it out. I kept the loop and got it working with this...

USE md0577283
DECLARE @num1 int = 10;
DECLARE @num2 int = 20;
DECLARE @count INT = 1;

WHILE @count < @num1
BEGIN
IF (@num1 % @count = 0 and @num2 % @count = 0)
PRINT @count
set @count = @count + 1
END

SQL + print common factors of two numbers

2 Answers2