when run query error:
procedure sp_get_random_questions, line 40 1 expression can specified in select list when subquery not introduced exists. msg 116, level 16, state 1, procedure sp_get_random_questions, line 52 1 expression can specified in select list when subquery not introduced exists.
this stored procedure, meant give random questions, @easy, @medium, @hard number of questions that'll randomized. if user chooses give @num, i'll divide number 3 (the 3 dificulties) , random questions.
select case when @num=0 ( select * ( select top (@easy) * tbl_perguntas p p.category=1 order newid()) union select * (select top (@medium) * tbl_perguntas p p.category=2 order newid()) b union select * (select top (@hard) * tbl_perguntas p p.category=3 order newid()) c) else ( select * (select top (@num/3) * tbl_perguntas p p.category=1 order newid()) union select * (select top (@num/3) * tbl_perguntas p p.category=2 order newid()) b union select * (select top (@num/3) * tbl_perguntas p p.category=3 order newid()) c ) end
as comments have stated, need use if/else control-of-flow, try this:
if @num=0 begin select top (@easy) * tbl_perguntas p p.category=1 order newid() union select top (@medium) * tbl_perguntas p p.category=2 order newid() union select top (@hard) * tbl_perguntas p p.category=3 order newid()) end else begin select top (@num/3) * tbl_perguntas p p.category=1 order newid() union select top (@num/3) * tbl_perguntas p p.category=2 order newid() union select top (@num/3) * tbl_perguntas p p.category=3 order newid()) end
Comments
Post a Comment