how to increment a value inside stored procedure

Question

How to increment a value of local variable in the loop inside a stored procedure

ALTER PROC [dbo].[Usp_SelectQuestion]
@NoOfQuestion int
AS
BEGIN
Declare @CNT int
Declare @test int
Declare @x int
Declare @y int
set @x = 1;
set @y = 1; 
Select @CNT=(Select Count(*) from (select Distinct(setno)from onlin) AS A)
select @test=@NoOfQuestion/@CNT
while @x <= @CNT do 
    while @y <= @test
        select  * from onlin where setno = @x
        set @y = @y +1
    set @x =@x + 1 
END

the values like @x and @y is not incrementing and I am stuck in an infinite loop.

***SQL*** is just the *Structured Query Language* - a language used by many database systems, but not a a database product... many things are vendor-specific - so we really need to know what **database system** (and which version) you're using.... — marc_s, Dec 19 '12 at 07:29
@marc_s - I'm guessing the OP is using T-SQL (MS Sql Server). `set @x = @x1 + 1` is definitely OK - it should do what he wants. But the two "while" loops do *NOT* look correct. — paulsm4, Dec 19 '12 at 07:35

score 4 · Accepted Answer · answered Dec 19 '12 at 07:35

4

you have to enclose the while-body in a begin-end block.

while @x <= @CNT do begin
  while @y <= @test begin
    select  * from onlin where setno = @x
    set @y = @y + 1
  end
set @x =@x + 1
end

I do not understand what you are trying to achive (besides the fact that you want to increase some variables)

answered Dec 19 '12 at 07:35

alzaimar

4,572
1
16
30

i have a table it consist of column like (questionid,questionsetno,scale,question) if someone give total no of question is 50, assume i have 5 set of question paper(questionsetno) so i want 10 question from each set randomly – Prathiesh Dec 19 '12 at 07:47
Then look up SO for a solution ;-) http://stackoverflow.com/questions/848872/select-n-random-rows-from-sql-server-table – alzaimar Dec 19 '12 at 18:47

how to increment a value inside stored procedure

1 Answers1