Let's say I have the following simple table variable:
declare @databases table ( DatabaseID int, Name varchar(15), Server varchar(15) ) -- insert a bunch rows into @databases Is declaring and using a cursor my only option if I wanted to iterate through the rows? Is there another way?
921 Answers
First of all you should be absolutely sure you need to iterate through each row — set based operations will perform faster in every case I can think of and will normally use simpler code.
Depending on your data it may be possible to loop using just SELECT statements as shown below:
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 Another alternative is to use a temporary table:
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 The option you should choose really depends on the structure and volume of your data.
Note: If you are using SQL Server you would be better served using:
WHILE EXISTS(SELECT * FROM #Temp) Using COUNT will have to touch every single row in the table, the EXISTS only needs to touch the first one (see Josef's answer below).
Just a quick note, if you are using SQL Server (2008 and above), the examples that have:
While (Select Count(*) From #Temp) > 0 Would be better served with
While EXISTS(SELECT * From #Temp) The Count will have to touch every single row in the table, the EXISTS only needs to touch the first one.
This is how I do it:
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 No Cursors, no temporary tables, no extra columns. The USERID column must be a unique integer, as most Primary Keys are.
1Define your temp table like this -
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 Then do this -
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 Here is how I would do it:
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 [Edit] Because I probably skipped the word "variable" when I first time read the question, here is an updated response...
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 If you have no choice than to go row by row creating a FAST_FORWARD cursor. It will be as fast as building up a while loop and much easier to maintain over the long haul.
FAST_FORWARD Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if SCROLL or FOR_UPDATE is also specified.
1This will work in SQL SERVER 2012 version.
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 Another approach without having to change your schema or using temp tables:
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 You can use a while loop:
While (Select Count(*) From #TempTable) > 0 Begin Insert Into @Databases... Delete From #TempTable Where x = x End Lightweight, without having to make extra tables, if you have an integer ID on the table
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 I really do not see the point why you would need to resort to using dreaded cursor. But here is another option if you are using SQL Server version 2005/2008
Use 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 -- [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 It's possible to use a cursor to do this:
create function [dbo].f_teste_loop returns @tabela table ( cod int, nome varchar(10) ) as begin
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; end
create procedure [dbo].[sp_teste_loop] as begin
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; end
1I'm going to provide the set-based solution.
insert @databases (DatabaseID, Name, Server) select DatabaseID, Name, Server From ... (Use whatever query you would have used in the loop or cursor) This is far faster than any looping techique and is easier to write and maintain.
I prefer using the Offset Fetch if you have a unique ID you can sort your table by:
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 This way I don't need to add fields to the table or use a window function.
0I agree with the previous post that set-based operations will typically perform better, but if you do need to iterate over the rows here's the approach I would take:
- Add a new field to your table variable (Data Type Bit, default 0)
- Insert your data
- Select the Top 1 Row where fUsed = 0 (Note: fUsed is the name of the field in step 1)
- Perform whatever processing you need to do
- Update the record in your table variable by setting fUsed = 1 for the record
Select the next unused record from the table and repeat the process
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
Step1: Below select statement creates a temp table with unique row number for each record.
select eno,ename,eaddress,mobno int,row_number() over(order by eno desc) as rno into #tmp_sri from emp Step2:Declare required variables
DECLARE @ROWNUMBER INT DECLARE @ename varchar(100) Step3: Take total rows count from temp table
SELECT @ROWNUMBER = COUNT(*) FROM #tmp_sri declare @rno int Step4: Loop temp table based on unique row number create in 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 This approach only requires one variable and does not delete any rows from @databases. I know there are a lot of answers here, but I don't see one that uses MIN to get your next ID like this.
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 Here's my solution, which makes use of an infinite loop, the BREAK statement, and the @@ROWCOUNT function. No cursors or temporary table are necessary, and I only need to write one query to get the next row in the @databases table:
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 This is the code that I am using 2008 R2. This code that I am using is to build indexes on key fields (SSNO & EMPR_NO) n all tales
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 SELECT @pk = @pk + 1 would be better:
SET @pk += @pk Avoid using SELECT if you are not referencing tables are are just assigning values.
0
