Go to the next row of a table with while. Is it possible?

Asked

Viewed 98 times

-1

I wonder if without a cursor, I have how to scroll through a table within a While. Example, I have this block:

SET @CONT = 0
WHILE @cont < 3
BEGIN
 EXEC GP_ImprimeConsProdutosTipo ' and f.CodMaterial in (SELECT * FROM #PartNumberTemp)', 'SP', '', 0, 1, 0, 0,1,0, ''
 SET @cont = @cont + 1
END

var @cont has already been declared and so on. This query always brings the same result 3 times, because the parameter that is a select always takes the first record. tabtemp has 3 records, so proc execution should bring 3 distinct records and brings me 3 repeated records.

There is how to do this, simulate a next without a cursor in a table?

Tabtemp: #Partnumbertemp

I did it that way and it rolled, but I get the feeling of Gambi. As for the initial project, we changed and no longer needed this resource, but wanted to try to learn. At the time of that post the bank was unavailable and could not test the ricidleiv example, as soon as I can test and post. Here’s how I did

WHILE @cont < 5
BEGIN
 SET @novovalor = (select partnumber from #PartNumberTemp where idx = @cont)
 SET @whereproc = ' and f.CodMaterial = ''' + @novovalor + ''''
 EXEC GP_ImprimeConsProdutosTipo @whereproc, 'SP', '', 0, 1, 0, 0,1,0, ''
 SET @cont = @cont + 1
END

I created a field in Tabtemp IDX guy int and selected according to the value of IDX(Where)

sql sql-server

  • If I run proc out of the while block only returns me one record and not three and always the first

  • I don’t understand where the @cont variable influences the select

  • Or is to do the same select 3 times?

  • does not influence, in fact I did it on hand for tests.there are 3 records in the table and the proc must be executed 3 times passing a partnumber. 3, should return 3 records from proc. a @cont is just for testing a while. I get 3 records, but same

  • @Reginaldorigo, like this. In proc there is a select that takes this parameter in his Where: ' and f.codmaterial...'. Codmaterial and Partnumber being the same thing. Then for each past Partnumber, proc returns me a record with description, price, tax and etc.. If I step 3 partnumber, I will receive 3 different records. In the way I did this does not happen, because in while only takes the first record(no next) in the select of the tabtemp, understand

  • 1

    Yes. I think so. It has to be written differently, because the way it is, the parameter does not change. You have to tie @cont to the or select top(@cont) order by something parameter and return the last or all. I don’t know. rsrs

  • @Reginaldorigo, I get it. I think that way without cursor, it doesn’t roll. Everything you do will look like Gambi. Anyway thank you.

  • Spend a WHERE within a parameter to concatenate with a SQL is a lousy practice. I advise reviewing the problem to find a more appropriate solution to this problem

Show 3 more comments

1 answer

1

Without using a cursor, you can use the ROW_NUMBER() strategy and filter the line with each iteration according to the value of @Count.

This way, you would need to change the SELECT behind the Stored Procedure GP_ImprimeConsProdutosTipo, adding a new column and filtering by the counter parameter, example:

WITH CTE_CONSULTA
AS 
(
    SELECT 
        ...
        ROW_NUMBER() OVER(ORDER BY [id]) AS NumeroDaLinha
    FROM ...
)
SELECT * FROM CTE_CONSULTA
WHERE NumeroDaLinha = @contador

  • I think in most scenarios, the use of CURSOR is the most performatic and recommendable.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.