t sql - How to write a foreach in SQL Server?

link之家
链接快照平台
输入网页链接，自动生成快照
标签化管理网页链接
Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
I am trying to achieve something along the lines of a for-each, where I would like to take the Ids of a returned select statement and use each of them.
DECLARE @i int
DECLARE @PractitionerId int
DECLARE @numrows int
DECLARE @Practitioner TABLE (
    idx smallint Primary Key IDENTITY(1,1)
    , PractitionerId int
INSERT @Practitioner
SELECT distinct PractitionerId FROM Practitioner
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM Practitioner)
IF @numrows > 0
    WHILE (@i <= (SELECT MAX(idx) FROM Practitioner))
    BEGIN
        SET @PractitionerId = (SELECT PractitionerId FROM @Practitioner WHERE idx = @i)
        --Do something with Id here
        PRINT @PractitionerId
        SET @i = @i + 1
At the moment I have something that looks like the above, but am getting the error:
Invalid column name 'idx'.
                How to iterate through a result set by using Transact-SQL in SQL Server: support.microsoft.com/kb/111401/nl
– Anonymoose
                Aug 29, 2013 at 14:42
                idx is in @Practitioner not Practitioner. There are most often superior set-based alternatives to a for-each approach, if you show what you do with the row value perhaps an alternative can be suggested.
– Alex K.
                Aug 29, 2013 at 14:43
                Please post more about what you're trying to accomplish.  Avoid RBAR like the plague (99% of the time).        simple-talk.com/sql/t-sql-programming/…
– granadaCoder
                Aug 29, 2013 at 14:58
                If you tell us what --Do something with Id here is, it is likely we can show you how to solve this problem without any loops or cursors. In most cases, you want to use a set-based solution, since that is how SQL Server is optimized to work. Looping and treating one row at a time certainly has its place, but I suspect this isn't it.
– Aaron Bertrand
                Aug 29, 2013 at 15:07
You seem to want to use a CURSOR. Though most of the times it's best to use a set based solution, there are some times where a CURSOR is the best solution. Without knowing more about your real problem, we can't help you more than that:
DECLARE @PractitionerId int
DECLARE MY_CURSOR CURSOR 
  LOCAL STATIC READ_ONLY FORWARD_ONLY
SELECT DISTINCT PractitionerId 
FROM Practitioner
OPEN MY_CURSOR
FETCH NEXT FROM MY_CURSOR INTO @PractitionerId
WHILE @@FETCH_STATUS = 0
BEGIN 
    --Do something with Id here
    PRINT @PractitionerId
    FETCH NEXT FROM MY_CURSOR INTO @PractitionerId
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR
                PLEASE do not start using cursors left and right.  They are needed <1% of the time.  RBAR (row by agonizing row) solutions are typically bad performers and cause headaches.  If you are new, PLEASE try to learn this lesson early.
– granadaCoder
                Aug 29, 2013 at 14:56
                Of course, if you want to actually do something with each record in your loop (like send an email or write to a file) rather than just shuffle stuff around within SQL, you need to use a cursor or something similar (not set logic). Likely people who come to SQL and try to do something RBAR they are coming to it as a programmer who needs it to do something useful like this.
– TylerH
                Sep 17, 2020 at 22:06
Suppose that the column PractitionerId is a unique, then you can use the following loop
DECLARE @PractitionerId int = 0
WHILE(1 = 1)
BEGIN
  SELECT @PractitionerId = MIN(PractitionerId)
  FROM dbo.Practitioner WHERE PractitionerId > @PractitionerId
  IF @PractitionerId IS NULL BREAK
  SELECT @PractitionerId
                Too simple to be true. You are selecting MIN(PractitionerId) always inside the loop. What is the condition to exit the loop? looks like an infinite loop to me.
– bluelabel
                Dec 17, 2015 at 5:14
                @bluelabel to exit loop script has the following condition IF PractitionerId IS NULL BREAK
– Aleksandr Fedorenko
                Dec 17, 2015 at 8:22
                How interesting. Using the old value of @PractitionerId in the WHERE clause to limit the input to the MIN function before setting the new value of @PractitionerId. Effective, but not obvious to us non-guru SQL users.
– Jesse Chisholm
                Dec 15, 2020 at 2:53
This generally (almost always) performs better than a cursor and is simpler:
DECLARE @PractitionerList TABLE(PracticionerID INT)
DECLARE @PracticionerID INT
INSERT @PractitionerList(PracticionerID)
SELECT PracticionerID
FROM Practitioner
WHILE(1 = 1)
BEGIN
    SET @PracticionerID = NULL
    SELECT TOP(1) @PracticionerID = PracticionerID
    FROM @PractitionerList
    IF @PracticionerID IS NULL
        BREAK
    PRINT 'DO STUFF'
    DELETE TOP(1) FROM @PractitionerList
Your select count and select max should be from your table variable instead of the actual table
DECLARE @i int
DECLARE @PractitionerId int
DECLARE @numrows int
DECLARE @Practitioner TABLE (
    idx smallint Primary Key IDENTITY(1,1)
    , PractitionerId int
INSERT @Practitioner
SELECT distinct PractitionerId FROM Practitioner
SET @i = 1
SET @numrows = (SELECT COUNT(*) FROM @Practitioner)
IF @numrows > 0
    WHILE (@i <= (SELECT MAX(idx) FROM @Practitioner))
    BEGIN
        SET @PractitionerId = (SELECT PractitionerId FROM @Practitioner WHERE idx = @i)
        --Do something with Id here
        PRINT @PractitionerId
        SET @i = @i + 1
I would say everything probably works except that the column idx doesn't actually exist in the table you're selecting from. Maybe you meant to select from @Practitioner:
WHILE (@i <= (SELECT MAX(idx) FROM @Practitioner))
because that's defined in the code above like that:
DECLARE @Practitioner TABLE (
    idx smallint Primary Key IDENTITY(1,1)
    , PractitionerId int
The following line is wrong in your version:
WHILE (@i <= (SELECT MAX(idx) FROM @Practitioner))
(Missing the @)
Might be an idea to change your naming convention so that the tables are more different.
            DECLARE @curren_val int
            DECLARE @numrows int
            create table #Practitioner (idx int IDENTITY(1,1), PractitionerId int)
            INSERT INTO #Practitioner (PractitionerId) values (10),(20),(30)
            SET @i = 1
            SET @numrows = (SELECT COUNT(*) FROM #Practitioner)
            IF @numrows > 0
            WHILE (@i <= (SELECT MAX(idx) FROM #Practitioner))
            BEGIN
                SET @curren_val = (SELECT PractitionerId FROM #Practitioner WHERE idx = @i)
                --Do something with Id here
                PRINT @curren_val
                SET @i = @i + 1
  Here i've add some values in the table beacuse, initially it is empty.
We can access or we can do anything in the body of the loop and we can access the idx by defining it inside the table definition.
              BEGIN
                SET @curren_val = (SELECT PractitionerId FROM #Practitioner WHERE idx = @i)
                --Do something with Id here
                PRINT @curren_val
                SET @i = @i + 1
EXEC PRC_FOREACH
    #A --Table we want to do the FOREACH
    , 'SELECT @I, @J' --The execute command, each column becomes a variable in the same type, so DON'T USE SPACES IN NAMES
   --The third variable is the database, it's optional because a table in TEMPB or the DB of the proc will be discovered in code
The result is 2 selects for each row.
The syntax of UPDATE and break the FOREACH are written in the hints.
This is the proc code:
CREATE PROC [dbo].[PRC_FOREACH] (@TBL VARCHAR(100) = NULL, @EXECUTE NVARCHAR(MAX)=NULL, @DB VARCHAR(100) = NULL) AS BEGIN
    --LOOP BETWEEN EACH TABLE LINE            
IF @TBL + @EXECUTE IS NULL BEGIN
    PRINT '@TBL: A TABLE TO MAKE OUT EACH LINE'
    PRINT '@EXECUTE: COMMAND TO BE PERFORMED ON EACH FOREACH TRANSACTION'
    PRINT '@DB: BANK WHERE THIS TABLE IS (IF NOT INFORMED IT WILL BE DB_NAME () OR TEMPDB)' + CHAR(13)
    PRINT 'ROW COLUMNS WILL VARIABLE WITH THE SAME NAME (COL_A = @COL_A)'
    PRINT 'THEREFORE THE COLUMNS CANT CONTAIN SPACES!' + CHAR(13)
    PRINT 'SYNTAX UPDATE:
UPDATE TABLE
SET COL = NEW_VALUE
WHERE CURRENT OF MY_CURSOR
CLOSE CURSOR (BEFORE ALL LINES):
IF 1 = 1 GOTO FIM_CURSOR'
    RETURN
SET @DB = ISNULL(@DB, CASE WHEN LEFT(@TBL, 1) = '#' THEN 'TEMPDB' ELSE DB_NAME() END)
    --Identifies the columns for the variables (DECLARE and INTO (Next cursor line))
DECLARE @Q NVARCHAR(MAX)
SET @Q = '
WITH X AS (
    SELECT
        A = '', @'' + NAME
        , B = '' '' + type_name(system_type_id)
        , C = CASE
            WHEN type_name(system_type_id) IN (''VARCHAR'', ''CHAR'', ''NCHAR'', ''NVARCHAR'') THEN ''('' + REPLACE(CONVERT(VARCHAR(10), max_length), ''-1'', ''MAX'') + '')''
            WHEN type_name(system_type_id) IN (''DECIMAL'', ''NUMERIC'') THEN ''('' + CONVERT(VARCHAR(10), precision) + '', '' + CONVERT(VARCHAR(10), scale) + '')''
            ELSE ''''
    FROM [' + @DB + '].SYS.COLUMNS C WITH(NOLOCK)
    WHERE OBJECT_ID = OBJECT_ID(''[' + @DB + '].DBO.[' + @TBL + ']'')
SELECT
    @DECLARE = STUFF((SELECT A + B + C FROM X FOR XML PATH('''')), 1, 1, '''')
    , @INTO = ''--Read the next line
FETCH NEXT FROM MY_CURSOR INTO '' + STUFF((SELECT A + '''' FROM X FOR XML PATH('''')), 1, 1, '''')'
DECLARE @DECLARE NVARCHAR(MAX), @INTO NVARCHAR(MAX)
EXEC SP_EXECUTESQL @Q, N'@DECLARE NVARCHAR(MAX) OUTPUT, @INTO NVARCHAR(MAX) OUTPUT', @DECLARE OUTPUT, @INTO OUTPUT
    --PREPARE TO QUERY
SELECT
    @Q = '
DECLARE ' + @DECLARE + '
-- Cursor to scroll through object names
DECLARE MY_CURSOR CURSOR FOR
    SELECT *
    FROM [' + @DB + '].DBO.[' + @TBL + ']
-- Opening Cursor for Reading
OPEN MY_CURSOR
' + @INTO + '
-- Traversing Cursor Lines (While There)
WHILE @@FETCH_STATUS = 0
BEGIN
    ' + @EXECUTE + '
    -- Reading the next line
    ' + @INTO + '
FIM_CURSOR:
-- Closing Cursor for Reading
CLOSE MY_CURSOR
DEALLOCATE MY_CURSOR'
EXEC SP_EXECUTESQL @Q --MAGIA
I came up with a very effective and (I think) readable way to do this.
Create a temp table and put the records you want to iterate in there
Use WHILE @@ROWCOUNT <> 0 to do the iterating
To get one row at a time do, SELECT TOP 1 <fieldnames>
b. save the unique ID for that row in a variable
Do stuff, then delete the row from the temp table based on the ID saved at step 3b.
Here's the code. Sorry, its using my variable names instead of the ones in the question.
DECLARE @tempPFRunStops TABLE (
    ProformaRunStopsID int,
    ProformaRunMasterID int,
    CompanyLocationID int,
    StopSequence int
INSERT @tempPFRunStops (ProformaRunStopsID, ProformaRunMasterID, CompanyLocationID, StopSequence)
SELECT 
    ProformaRunStopsID, 
    ProformaRunMasterID, 
    CompanyLocationID, 
    StopSequence 
FROM ProformaRunStops
WHERE ProformaRunMasterID IN ( 
    SELECT ProformaRunMasterID 
    FROM ProformaRunMaster 
    WHERE ProformaId = 15 )
-- SELECT * FROM @tempPFRunStops
WHILE @@ROWCOUNT <> 0  -- << I dont know how this works
BEGIN
    SELECT TOP 1 * FROM @tempPFRunStops
    -- I could have put the unique ID into a variable here
    SELECT 'Ha'  -- Do Stuff
    DELETE @tempPFRunStops 
    WHERE ProformaRunStopsID = (SELECT TOP 1 ProformaRunStopsID FROM @tempPFRunStops)
Use temporary "index" table, which will have iterator column;
Use sort of "iterator.next()" technique;
Index table cons/pros:
"+" Easy to understand;
"+" Universally suitable for any query structure;
"+" Progress calculated with count variable;
"-" Row or count could change during execution leading to errors;
"-" Needs heavy distinct operator;
"-" Requires space for temp table. Bad when huge data involved.
iterator.next() cons/pros:
"+" Adapts to changing count during execution;
"-" Requires ordered columns to be index covered;
"-" No count for progress calculation;
"+" No need to preallocate all iterations beforehand into temp table. Very good for huge tables.
"+" No aggregations usage like distinct or max. Very good for huge tables.
"+" Very fast for huge tables with index coverage;
Index approach
drop table if exists #UniquePractitioners;
create table #UniquePractitioners(
    RowId int not null identity(1, 1) primary key clustered,
    PractitionerId int not null
insert into #UniquePractitioners(PractitionerId)
select distinct t.PractitionerId
from dbo.Practitioner t
order by t.PractitionerId asc;
declare @count int = @@rowcount;
declare @i int = 0;
declare @PractitionerId int;
while (@i < @count)
begin
    set @i += 1;
    select @PractitionerId = t.PractitionerId 
    from #UniquePractitioners t 
    where t.RowId = @i;
    if @@rowcount = 0
        break;
    print concat('Iteration: ', @i, ' / ', @count, ', @PractitionerId: ', @PractitionerId);
iterator.next() approach
declare @i int = 0;
declare @PractitionerId int = 0;
while (1=1)
begin
    set @i += 1;
    select top (1) @PractitionerId = t.PractitionerId 
    from dbo.Practitioner t 
    where t.PractitionerId > @PractitionerId
    order by t.PractitionerId asc;
    if @@rowcount = 0
        break;
    print concat('Iteration: ', @i, ', @PractitionerId: ', @PractitionerId);
Analysis
Say we have Users (UserId PK) table, for each row of it we should execute some processing (on other tables for example).
If you simply going to iterate each row for that table there is no need to create additional "Index" table, as UserId column is lready ordered and covered by an index. Therefore iteration.next() approach would be the most efficient.
But if you are determining with some heavy query, only subset of users, then do universal "Index table" approach.
For BE developers
Try to use iteration.next() with batching always, instead of loading full table.
        Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.