while tabla recorrer for sql-server tsql loops

sql server - for - ¿Hay una manera de recorrer una variable de tabla en TSQL sin usar un cursor?



while select sql server (21)

Aquí está mi solución, que utiliza un bucle infinito, la declaración BREAK y la función @@ROWCOUNT . No se necesitan cursores ni tabla temporal, y solo necesito escribir una consulta para obtener la siguiente fila en la tabla de @databases :

declare @databases table ( DatabaseID int, [Name] varchar(15), [Server] varchar(15) ); -- Populate the [@databases] table with test data. insert into @databases (DatabaseID, [Name], [Server]) select X.DatabaseID, X.[Name], X.[Server] from (values (1, ''Roger'', ''ServerA''), (5, ''Suzy'', ''ServerB''), (8675309, ''Jenny'', ''TommyTutone'') ) X (DatabaseID, [Name], [Server]) -- Create an infinite loop & ensure that a break condition is reached in the loop code. declare @databaseId int; while (1=1) begin -- Get the next database ID. select top(1) @databaseId = DatabaseId from @databases where DatabaseId > isnull(@databaseId, 0); -- If no rows were found by the preceding SQL query, you''re done; exit the WHILE loop. if (@@ROWCOUNT = 0) break; -- Otherwise, do whatever you need to do with the current [@databases] table row here. print ''Processing @databaseId #'' + cast(@databaseId as varchar(50)); end

Digamos que tengo la siguiente variable de tabla simple:

declare @databases table ( DatabaseID int, Name varchar(15), Server varchar(15) ) -- insert a bunch rows into @databases

¿Es mi única opción declarar y usar un cursor si quisiera recorrer las filas? ¿Hay otra manera?


Así es como lo hago:

declare @RowNum int, @CustId nchar(5), @Name1 nchar(25) select @CustId=MAX(USERID) FROM UserIDs --start with the highest ID Select @RowNum = Count(*) From UserIDs --get total number of records WHILE @RowNum > 0 --loop until no more records BEGIN select @Name1 = username1 from UserIDs where USERID= @CustID --get other info from that row print cast(@RowNum as char(12)) + '' '' + @CustId + '' '' + @Name1 --do whatever select top 1 @CustId=USERID from UserIDs where USERID < @CustID order by USERID desc--get the next one set @RowNum = @RowNum - 1 --decrease count END

Sin cursores, sin tablas temporales, sin columnas adicionales. La columna USERID debe ser un entero único, como lo son la mayoría de las claves primarias.


Así es como lo haría:

Select Identity(int, 1,1) AS PK, DatabaseID Into #T From @databases Declare @maxPK int;Select @maxPK = MAX(PK) From #T Declare @pk int;Set @pk = 1 While @pk <= @maxPK Begin -- Get one record Select DatabaseID, Name, Server From @databases Where DatabaseID = (Select DatabaseID From #T Where PK = @pk) --Do some processing here -- Select @pk = @pk + 1 End

[Editar] Debido a que probablemente salté la palabra "variable" la primera vez que leí la pregunta, aquí hay una respuesta actualizada ...

declare @databases table ( PK int IDENTITY(1,1), DatabaseID int, Name varchar(15), Server varchar(15) ) -- insert a bunch rows into @databases --/* INSERT INTO @databases (DatabaseID, Name, Server) SELECT 1,''MainDB'', ''MyServer'' INSERT INTO @databases (DatabaseID, Name, Server) SELECT 1,''MyDB'', ''MyServer2'' --*/ Declare @maxPK int;Select @maxPK = MAX(PK) From @databases Declare @pk int;Set @pk = 1 While @pk <= @maxPK Begin /* Get one record (you can read the values into some variables) */ Select DatabaseID, Name, Server From @databases Where PK = @pk /* Do some processing here */ /* ... */ Select @pk = @pk + 1 End


Define tu tabla temporal de esta manera:

declare @databases table ( RowID int not null identity(1,1) primary key, DatabaseID int, Name varchar(15), Server varchar(15) ) -- insert a bunch rows into @databases

Entonces haz esto ...

declare @i int select @i = min(RowID) from @databases declare @max int select @max = max(RowID) from @databases while @i <= @max begin select DatabaseID, Name, Server from @database where RowID = @i --do some stuff set @i = @i + 1 end


En primer lugar, debe estar absolutamente seguro de que debe recorrer cada fila; las operaciones basadas en conjuntos se realizarán más rápido en todos los casos que se me ocurran y normalmente utilizaremos un código más simple.

Dependiendo de sus datos, puede ser posible hacer un bucle simplemente usando las declaraciones de selección como se muestra a continuación:

Declare @Id int While (Select Count(*) From ATable Where Processed = 0) > 0 Begin Select Top 1 @Id = Id From ATable Where Processed = 0 --Do some processing here Update ATable Set Processed = 1 Where Id = @Id End

Otra alternativa es usar una tabla temporal:

Select * Into #Temp From ATable Declare @Id int While (Select Count(*) From #Temp) > 0 Begin Select Top 1 @Id = Id From #Temp --Do some processing here Delete #Temp Where Id = @Id End

La opción que debe elegir realmente depende de la estructura y el volumen de sus datos.

Nota: Si está utilizando SQL Server, sería mejor servirlo utilizando:

WHILE EXISTS(SELECT * FROM #Temp)

Al usar COUNT deberá tocar cada fila de la tabla, el EXISTS solo necesita tocar la primera (vea la respuesta de Josef a continuación).


Es posible usar un cursor para hacer esto:

La función de creación [dbo] .f_teste_loop devuelve la tabla @tabela (cod int, nome varchar (10)) como comienza

insert into @tabela values (1, ''verde''); insert into @tabela values (2, ''amarelo''); insert into @tabela values (3, ''azul''); insert into @tabela values (4, ''branco''); return;

fin

crear procedimiento [dbo]. [sp_teste_loop] como comenzar

DECLARE @cod int, @nome varchar(10); DECLARE curLoop CURSOR STATIC LOCAL FOR SELECT cod ,nome FROM dbo.f_teste_loop(); OPEN curLoop; FETCH NEXT FROM curLoop INTO @cod, @nome; WHILE (@@FETCH_STATUS = 0) BEGIN PRINT @nome; FETCH NEXT FROM curLoop INTO @cod, @nome; END CLOSE curLoop; DEALLOCATE curLoop;

fin


Este enfoque solo requiere una variable y no elimina ninguna fila de @databases. Sé que hay muchas respuestas aquí, pero no veo una que use MIN para obtener su próxima identificación de esta manera.

DECLARE @databases TABLE ( DatabaseID int, Name varchar(15), Server varchar(15) ) -- insert a bunch rows into @databases DECLARE @CurrID INT SELECT @CurrID = MIN(DatabaseID) FROM @databases WHILE @CurrID IS NOT NULL BEGIN -- Do stuff for @CurrID SELECT @CurrID = MIN(DatabaseID) FROM @databases WHERE DatabaseID > @CurrID END


Este es el código que estoy usando 2008 R2. Este código que estoy usando es para construir índices en campos clave (SSNO y EMPR_NO) n todos los cuentos

if object_ID(''tempdb..#a'')is not NULL drop table #a select ''IF EXISTS (SELECT name FROM sysindexes WHERE name =''+CHAR(39)+''''+''IDX_''+COLUMN_NAME+''_''+SUBSTRING(table_name,5,len(table_name)-3)+char(39)+'')'' +'' begin DROP INDEX [IDX_''+COLUMN_NAME+''_''+SUBSTRING(table_name,5,len(table_name)-3)+''] ON ''+table_schema+''.''+table_name+'' END Create index IDX_''+COLUMN_NAME+''_''+SUBSTRING(table_name,5,len(table_name)-3)+ '' on ''+ table_schema+''.''+table_name+'' (''+COLUMN_NAME+'') '' ''Field'' ,ROW_NUMBER() over (order by table_NAMe) as ''ROWNMBR'' into #a from INFORMATION_SCHEMA.COLUMNS where (COLUMN_NAME like ''%_SSNO_%'' or COLUMN_NAME like''%_EMPR_NO_'') and TABLE_SCHEMA=''dbo'' declare @loopcntr int declare @ROW int declare @String nvarchar(1000) set @loopcntr=(select count(*) from #a) set @ROW=1 while (@ROW <= @loopcntr) begin select top 1 @String=a.Field from #A a where a.ROWNMBR = @ROW execute sp_executesql @String set @ROW = @ROW + 1 end


Esto funcionará en la versión SQL SERVER 2012.

declare @Rowcount int select @Rowcount=count(*) from AddressTable; while( @Rowcount>0) begin select @Rowcount=@Rowcount-1; SELECT * FROM AddressTable order by AddressId desc OFFSET @Rowcount ROWS FETCH NEXT 1 ROWS ONLY; end


Estoy de acuerdo con la publicación anterior de que las operaciones basadas en conjuntos normalmente se desempeñarán mejor, pero si necesita iterar sobre las filas, este es el enfoque que tomaría:

  1. Agregue un nuevo campo a su variable de tabla (Bit de tipo de datos, predeterminado 0)
  2. Inserta tus datos
  3. Seleccione la fila superior 1 donde fUsed = 0 (Nota: fUsed es el nombre del campo en el paso 1)
  4. Realice cualquier procesamiento que necesite hacer
  5. Actualice el registro en su variable de tabla estableciendo fUsed = 1 para el registro
  6. Seleccione el siguiente registro no utilizado de la tabla y repita el proceso

    DECLARE @databases TABLE ( DatabaseID int, Name varchar(15), Server varchar(15), fUsed BIT DEFAULT 0 ) -- insert a bunch rows into @databases DECLARE @DBID INT SELECT TOP 1 @DBID = DatabaseID from @databases where fUsed = 0 WHILE @@ROWCOUNT <> 0 and @DBID IS NOT NULL BEGIN -- Perform your processing here --Update the record to "used" UPDATE @databases SET fUsed = 1 WHERE DatabaseID = @DBID --Get the next record SELECT TOP 1 @DBID = DatabaseID from @databases where fUsed = 0 END


Ligero, sin tener que hacer tablas adicionales, si tiene un ID entero en la tabla

Declare @id int = 0, @anything nvarchar(max) WHILE(1=1) BEGIN Select Top 1 @anything=[Anything],@id=@id+1 FROM Table WHERE ID>@id if(@@ROWCOUNT=0) break; --Process @anything END


Otro enfoque sin tener que cambiar su esquema o usar tablas temporales:

DECLARE @rowCount int = 0 ,@currentRow int = 1 ,@databaseID int ,@name varchar(15) ,@server varchar(15); SELECT @rowCount = COUNT(*) FROM @databases; WHILE (@currentRow <= @rowCount) BEGIN SELECT TOP 1 @databaseID = rt.[DatabaseID] ,@name = rt.[Name] ,@server = rt.[Server] FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY t.[DatabaseID], t.[Name], t.[Server] ) AS [RowNumber] ,t.[DatabaseID] ,t.[Name] ,t.[Server] FROM @databases t ) rt WHERE rt.[RowNumber] = @currentRow; EXEC [your_stored_procedure] @databaseID, @name, @server; SET @currentRow = @currentRow + 1; END


Paso 1: la siguiente instrucción de selección crea una tabla temporal con un número de fila único para cada registro.

select eno,ename,eaddress,mobno int,row_number() over(order by eno desc) as rno into #tmp_sri from emp

Paso 2: Declarar las variables requeridas.

DECLARE @ROWNUMBER INT DECLARE @ename varchar(100)

Paso 3: tomar el número total de filas de la tabla temporal

SELECT @ROWNUMBER = COUNT(*) FROM #tmp_sri declare @rno int

Paso 4: tabla de temperatura de bucle basada en un número de fila único creado en temp

while @rownumber>0 begin set @rno=@rownumber select @ename=ename from #tmp_sri where rno=@rno **// You can take columns data from here as many as you want** set @rownumber=@rownumber-1 print @ename **// instead of printing, you can write insert, update, delete statements** end


Prefiero usar la función de recuperación de compensación, si tiene una ID única, puede ordenar su tabla por:

DECLARE @TableVariable (ID int, Name varchar(50)); DECLARE @RecordCount int; SELECT @RecordCount = COUNT(*) FROM @TableVariable; WHILE @RecordCount > 0 BEGIN SELECT ID, Name FROM @TableVariable ORDER BY ID OFFSET @RecordCount - 1 FETCH NEXT 1 ROW; SET @RecordCount = @RecordCount - 1; END

De esta manera no necesito agregar campos a la tabla o usar una función de ventana.


Puedes usar un bucle while:

While (Select Count(*) From #TempTable) > 0 Begin Insert Into @Databases... Delete From #TempTable Where x = x End


Realmente no veo el punto por el que tendrías que recurrir al uso del cursor temido. Pero aquí hay otra opción si está utilizando SQL Server versión 2005/2008
Usar recursion

declare @databases table ( DatabaseID int, Name varchar(15), Server varchar(15) ) --; Insert records into @databases... --; Recurse through @databases ;with DBs as ( select * from @databases where DatabaseID = 1 union all select A.* from @databases A inner join DBs B on A.DatabaseID = B.DatabaseID + 1 ) select * from DBs


Seleccionar @pk = @pk + 1 sería mejor: SET @pk + = @pk. Evite utilizar SELECT si no hace referencia a tablas, solo están asignando valores.


Si no tiene más remedio que ir fila por fila, cree un cursor FAST_FORWARD. Será tan rápido como construir un bucle de tiempo y mucho más fácil de mantener a largo plazo.

FAST_FORWARD Especifica un cursor FORWARD_ONLY, READ_ONLY con las optimizaciones de rendimiento habilitadas. FAST_FORWARD no se puede especificar si también se especifica SCROLL o FOR_UPDATE.


Solo una nota rápida, si está utilizando SQL Server (2008 y superior), los ejemplos que tienen:

While (Select Count(*) From #Temp) > 0

Estaría mejor servido con

While EXISTS(SELECT * From #Temp)

El Conde tendrá que tocar cada fila en la tabla, el EXISTS solo necesita tocar la primera.


Voy a proporcionar la solución basada en conjuntos.

insert @databases (DatabaseID, Name, Server) select DatabaseID, Name, Server From ... (Use whatever query you would have used in the loop or cursor)

Esto es mucho más rápido que cualquier técnica de bucle y es más fácil de escribir y mantener.


-- [PO_RollBackOnReject] ''FININV10532'' alter procedure PO_RollBackOnReject @CaseID nvarchar(100) AS Begin SELECT * INTO #tmpTable FROM PO_InvoiceItems where CaseID = @CaseID Declare @Id int Declare @PO_No int Declare @Current_Balance Money While (Select ROW_NUMBER() OVER(ORDER BY PO_LineNo DESC) From #tmpTable) > 0 Begin Select Top 1 @Id = PO_LineNo, @Current_Balance = Current_Balance, @PO_No = PO_No From #Temp update PO_Details Set Current_Balance = Current_Balance + @Current_Balance, Previous_App_Amount= Previous_App_Amount + @Current_Balance, Is_Processed = 0 Where PO_LineNumber = @Id AND PO_No = @PO_No update PO_InvoiceItems Set IsVisible = 0, Is_Processed= 0 ,Is_InProgress = 0 , Is_Active = 0 Where PO_LineNo = @Id AND PO_No = @PO_No End End