sql - What am I doing wrong with this loop in MySQL -
here question i'm trying answer:
write script calculates common factors between 10 , 20. find common factor, can use modulo operator (%) check whether number can evenly divided both numbers. then, script should print lines display common factors this:
common factors of 10 , 20
1
2
5
here code:
use myguitarshop; if object_id ('test') not null drop procedure test; create procedure test () begin declare counter int declare fact10 int; declare fact20 int; declare factors varchar (100); set fact10 = 10; set fact20 = 20; set counter = 1; set factors = 'factors of 10 , 20'; while (counter <= 10/2) if (fact10 % of counter = 0 , fact20 % of counter = 0) set factors = contact (factors, counter, ' '); end; this error keep getting:
msg 102, level 15, state 1, procedure test, line 6 incorrect syntax near ')'. msg 155, level 15, state 2, procedure test, line 8 'int' not recognized cursor option. msg 155, level 15, state 2, procedure test, line 9 'int' not recognized cursor option. msg 155, level 15, state 2, procedure test, line 10 'int' not recognized cursor option. msg 155, level 15, state 2, procedure test, line 11 'varchar' not recognized cursor option. msg 102, level 15, state 1, procedure test, line 18 incorrect syntax near 'do'. msg 156, level 15, state 1, procedure test, line 19 incorrect syntax near keyword 'of'. i'm not sure i'm doing wrong appreciated.
the error message code indicates using ms sql server. if case need prefix local variables @ sign, , counter needs incremented in while loop or you'll stuck in infinite loop.
this code works mssql:
create procedure test begin declare @counter int declare @fact10 int declare @fact20 int declare @factors varchar (100) set @fact10 = 10; set @fact20 = 20; set @counter = 1; set @factors = 'factors of 10 , 20: ' + char(13); while (@counter <= 10/2) -- should <= 10 begin if ( @fact10 % @counter = 0 , @fact20 % @counter = 0) set @factors = concat (@factors, @counter, ' ', char(13)); set @counter = @counter + 1; end select @factors end; the output be:
factors of 10 , 20: 1 2 5 why want in sql besides programming exercise don't understand though.
using numbers table same thing simple select query:
-- table master..spt_values.numbers holds integers 0-2047 select number master..spt_values type = 'p' , number > 0 , 10 % number = 0 , 20 % number = 0