nested while

Question

nested while

SSCrazy Eights

Points: 9212

December 11, 2020 at 12:59 pm

#3819338

CREATE Table TABLEA(N varchar(50),Q int,R int)
ALTER PROCEDURE [dbo].[SP](@N varchar(100),@Q int=0,@R int=0)
AS
BEGIN
    DECLARE @I int = 1
    BEGIN
WHILE (@I <= @R)
BEGIN
        WHILE (@I <= @Q)
        BEGIN
            INSERT INTO [dbo].[TABLEA] SELECT @N,@Q,@R
        SET @Q = @Q - 1
        END
SET @R = @R -1
    END
END
END

exec [dbo].[SP] 'NameA',10,2

Current TableA values after executing SP:
NQR
NameA102
NameA92
NameA82
NameA72
NameA62
NameA52
NameA42
NameA32
NameA22
NameA12

But I need the values to be inserted again based on @R value . If R is 2 then then the result in 
TABLEA Should be as below..
NQR
NameA102
NameA92
NameA82
NameA72
NameA62
NameA52
NameA42
NameA32
NameA22
NameA12
NQR
NameA102
NameA92
NameA82
NameA72
NameA62
NameA52
NameA42
NameA32
NameA22
NameA12

Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Collins SSCrazy Points: 2157 More actions · Answer 1

Why is this necessary? Here's a solution using (two of) the fnTally function to generate the rows instead of WHILE loops. Re-using declared variables is generally not a good practice in SQL imo. Anyway, if Q and R become large-ish then this could blow up in a hurry. CROSS JOIN'ing two tally functions is not generally recommended but that's what you're asking for.

alter procedure dbo.sp(
  @n    varchar(100),
  @q    int=0,
  @r    int=0)
as
insert dbo.tablea(n, q, t) 
select @n, fnq.N, @r
from dbo.fnTally(1, @q) fnq
     cross join dbo.fnTally(1, @r) fnr;
go

exec dbo.sp 'NameA', 10, 2;

select * from dbo.tablea
order by n, q desc;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 2

thanks for the response but I need it only with a nested while loop.

ratbak Ten Centuries Points: 1358 More actions · Answer 3

ratbak

Ten Centuries

Points: 1358

December 11, 2020 at 4:39 pm

#3819450

homework?

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 4

mtz676

SSCrazy Eights

Points: 9212

December 11, 2020 at 4:49 pm

#3819451

abridged version of what I need

Steve Collins SSCrazy Points: 2157 More actions · Answer 5

mtz676 wrote:

thanks for the response but I need it only with a nested while loop.

Don't re-use @I. Come up with a new variable to store the counter for the inner loop.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

mtz676 SSCrazy Eights Points: 9212 More actions · Answer 6

got it

    DECLARE @I int = 1
DECLARE @R int = 2
DECLARE @Q int = 3
DECLARE @Q1 int
SET @Q1 =@Q
DECLARE @N varchar(100) 
SET @N = 'NameA'
WHILE (@I <= @R+1)
BEGIN
  WHILE (@I <= @Q)
  BEGIN
  SELECT @N as Name,@I,@R,@Q
  SET @Q = @Q - 1
  END
        SET @Q = @Q1
SET @R = @R -1
END


NameA123
NameA122
NameA121
NameA113
NameA112
NameA111
NameA103
NameA102
NameA101

This reply was modified 3 years, 11 months ago by mtz676.
This reply was modified 3 years, 11 months ago by mtz676.
This reply was modified 3 years, 11 months ago by mtz676.
This reply was modified 3 years, 11 months ago by mtz676.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 7

just wonder why people still insist in using cursors/while loops when a better solution was given.

for those seeing this thread and thinking its a good way to do- this approach is how you should NOT do it.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

mtz676 wrote:

abridged version of what I need

I'd recommend sharing what the unabridged version is because it will be in even more of a need of speed and a While loop or rCTE isn't going to be your friend there or when it come to maintainability of the code.

mtz676 wrote:

got it

    DECLARE @I int = 1
DECLARE @R int = 2
DECLARE @Q int = 3
DECLARE @Q1 int
SET @Q1 =@Q
DECLARE @N varchar(100) 
SET @N = 'NameA'
WHILE (@I <= @R+1)
BEGIN
  WHILE (@I <= @Q)
  BEGIN
  SELECT @N as Name,@I,@R,@Q
  SET @Q = @Q - 1
  END
        SET @Q = @Q1
SET @R = @R -1
END


NameA123
NameA122
NameA121
NameA113
NameA112
NameA111
NameA103
NameA102
NameA101

@mtz676 ...

Please, just so we know, why does this has to be a While loop? For example, will you be calling a stored procedure in every iteration of the loop?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)