Sql return generation limit

Question

I have this code, which generates number of periods, but I take this return to excel, I can define the amount of returns? I could only bring five, returns, I can limit?

DECLARE @serie INT, @ordem INT, @Periodo INT DECLARE @tbResultado TABLE ( id INT IDENTITY(1,1) ,IdCliente INT, Serie INT, Ordem INT, idPerido INT)

SET @serie = 9059 SET @ordem = 05288 SET @Periodo = 0



WHILE (@Periodo <= 670) BEGIN   IF (@serie < (SELECT nmSerieIni FROM tbSysPeriodo where id = @Periodo)) 
            WHILE (@serie NOT BETWEEN (SELECT TOP 1 nmSerieIni FROM tbSysPeriodo where id = @Periodo) AND (SELECT TOP 1 nmSerieFim FROM tbSysPeriodo where id = @Periodo))      BEGIN
                SET @serie = @serie + (select ABS(nmSerieIni - nmSerieFim)+1 from tbSysPeriodo where id = @Periodo)

        END

    IF (@serie > (SELECT TOP 1 nmSerieFim FROM tbSysPeriodo where id = @Periodo)) 
            WHILE (@serie NOT BETWEEN (SELECT TOP 1 nmSerieIni FROM tbSysPeriodo where id = @Periodo) AND (SELECT TOP 1 nmSerieFim FROM tbSysPeriodo where id = @Periodo))      BEGIN
                SET @serie = @serie - (select ABS(nmSerieIni - nmSerieFim)+1 from tbSysPeriodo where id = @Periodo)

        END
            INSERT INTO @tbResultado    SELECT TOP 10       S.idCliente,        S.nmSerie,          S.nmOrdem,      P.idPeriodo     FROM tbSorte S      JOIN tbSortePeriodo P       ON S.id = P.idSorte     WHERE S.nmSerie = @serie AND S.nmOrdem NOT IN (SELECT ORDEM FROM @tbResultado) AND P.idPeriodo = @Periodo   ORDER BY nmSerie DESC, ABS(nmOrdem - @ordem)


WHILE (@serie NOT BETWEEN (SELECT TOP 1 nmSerieIni FROM tbSysPeriodo where id = @Periodo) AND (SELECT TOP 1 nmSerieFim FROM tbSysPeriodo where id = @Periodo)) BEGIN

    SET @serie = ABS(@serie-(SELECT TOP 1  nmSerie FROM tbSorte S JOIN tbSortePeriodo P ON S.id = P.idSorte WHERE P.idPeriodo = @Periodo AND S.nmSerie NOT IN (SELECT Serie FROM @tbResultado)))

END

INSERT INTO @tbResultado    SELECT TOP 10       S.idCliente,        S.nmSerie,          S.nmOrdem,      P.idPeriodo     FROM tbSorte S      JOIN tbSortePeriodo P       ON S.id = P.idSorte     WHERE S.nmOrdem NOT IN (SELECT ORDEM FROM @tbResultado) AND P.idPeriodo = @Periodo  ORDER BY ABS(nmSerie - @serie),nmSerie DESC,ABS(nmOrdem - @ordem)

SET @Periodo = @Periodo + 1


END          SELECT * FROM @tbResultado

Answer 1

Good afternoon! Instead of using SELECT * FROM @tbResulted, because it does not use SELECT TOP 5 * FROM @tbResulted?

That way, instead of fetching all records from the @tbResulted table, it will only bring the determined amount in TOP.

Abs!

Answer 2

Please try then the command below, I changed only to insert limit to 5 records, instead of 10 which is.

DECLARE @serie INT, 
        @ordem INT, 
        @Periodo INT 
DECLARE @tbResultado TABLE ( id INT IDENTITY(1,1) ,
                             IdCliente INT, 
                             Serie INT, 
                             Ordem INT, 
                             idPerido INT)

SET @serie = 9059 
SET @ordem = 05288 
SET @Periodo = 0

WHILE (@Periodo <= 670) BEGIN   IF (@serie < (SELECT nmSerieIni FROM tbSysPeriodo where id = @Periodo)) 
    WHILE (@serie NOT BETWEEN (SELECT TOP 1 nmSerieIni FROM tbSysPeriodo where id = @Periodo) AND (SELECT TOP 1 nmSerieFim FROM tbSysPeriodo where id = @Periodo))      
        BEGIN
            SET @serie = @serie + (select ABS(nmSerieIni - nmSerieFim)+1 from tbSysPeriodo where id = @Periodo)
        END

    IF (@serie > (SELECT TOP 1 nmSerieFim FROM tbSysPeriodo where id = @Periodo)) 
            WHILE (@serie NOT BETWEEN (SELECT TOP 1 nmSerieIni FROM tbSysPeriodo where id = @Periodo) AND (SELECT TOP 1 nmSerieFim FROM tbSysPeriodo where id = @Periodo))      
                BEGIN
                    SET @serie = @serie - (select ABS(nmSerieIni - nmSerieFim)+1 from tbSysPeriodo where id = @Periodo)
                END
            INSERT INTO @tbResultado    SELECT TOP 5 S.idCliente,
                                                     S.nmSerie,
                                                     S.nmOrdem,
                                                     P.idPeriodo     
                                        FROM tbSorte S
                                        JOIN tbSortePeriodo P       
                                        ON S.id = P.idSorte     
                                        WHERE S.nmSerie = @serie 
                                        AND S.nmOrdem NOT IN (SELECT ORDEM 
                                                              FROM @tbResultado) 
                                        AND P.idPeriodo = @Periodo   
                                        ORDER BY nmSerie DESC, 
                                        ABS(nmOrdem - @ordem)

WHILE (@serie NOT BETWEEN (SELECT TOP 1 nmSerieIni FROM tbSysPeriodo where id = @Periodo) AND (SELECT TOP 1 nmSerieFim FROM tbSysPeriodo where id = @Periodo)) 
    BEGIN
        SET @serie = ABS(@serie-(SELECT TOP 1  nmSerie FROM tbSorte S JOIN tbSortePeriodo P ON S.id = P.idSorte WHERE P.idPeriodo = @Periodo AND S.nmSerie NOT IN (SELECT Serie FROM @tbResultado)))
    END

    INSERT INTO @tbResultado    SELECT TOP 5 S.idCliente,
                                             S.nmSerie,
                                             S.nmOrdem,
                                             P.idPeriodo     
                                FROM tbSorte S      
                                JOIN tbSortePeriodo P       
                                ON S.id = P.idSorte     
                                WHERE S.nmOrdem NOT IN (SELECT ORDEM 
                                                        FROM @tbResultado) 
                                AND P.idPeriodo = @Periodo  
                                ORDER BY ABS(nmSerie - @serie),
                                         nmSerie DESC,
                                         ABS(nmOrdem - @ordem)

SET @Periodo = @Periodo + 1


END          

SELECT * FROM @tbResultado

Answer 3

Good afternoon, but in case, it wouldn’t help, because if you see in WHILE, it goes from zero to 670, there are intervals.