Iterate Through a List of Strings in SQL Server

Iterate through a list of strings In SQL Server?

What you created is not a list but a table variable. So how to Iterate over a table. Below is a simple example and I think you can proceed after if you understand it:

(Note: Cursors are not efficient when it comes to performance and large tables)

DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('Data1')
INSERT INTO @MyList VALUES ('Data2')
INSERT INTO @MyList VALUES ('Data3')
INSERT INTO @MyList VALUES ('Data4')
INSERT INTO @MyList VALUES ('Data5')
INSERT INTO @MyList VALUES ('Data6')
INSERT INTO @MyList VALUES ('Data7')
INSERT INTO @MyList VALUES ('Data8')

DECLARE @value VARCHAR(50)

DECLARE db_cursor CURSOR FOR  
SELECT Value FROM @MyList
OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @value   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       PRINT @value

       -- PUT YOUR LOGIC HERE
       -- MAKE USE OR VARIABLE @value wich is Data1, Data2, etc...

       FETCH NEXT FROM db_cursor INTO @value   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Prints:

Data1
Data2
Data3
Data4
Data5
Data6
Data7
Data8

So you have inside @value variable Data, Data2 .. etc . I think this solves your problem.

Alternative way is using a WHILE loop + OFFSET + FETCH NEXT :

DECLARE @MyList TABLE (Value NVARCHAR(50))
INSERT INTO @MyList VALUES ('Data1')
INSERT INTO @MyList VALUES ('Data2')
INSERT INTO @MyList VALUES ('Data3')
INSERT INTO @MyList VALUES ('Data4')
INSERT INTO @MyList VALUES ('Data5')
INSERT INTO @MyList VALUES ('Data6')
INSERT INTO @MyList VALUES ('Data7')
INSERT INTO @MyList VALUES ('Data8')

DECLARE @COUNTER INT = 0;
DECLARE @MAX INT = (SELECT COUNT(*) FROM @MyList)
DECLARE @VALUE VARCHAR(50);

WHILE @COUNTER < @MAX
BEGIN

SET @VALUE = (SELECT VALUE FROM
      (SELECT (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) [index] , Value from @MyList) R 
       ORDER BY R.[index] OFFSET @COUNTER 
       ROWS FETCH NEXT 1 ROWS ONLY);

PRINT @VALUE

SET @COUNTER = @COUNTER + 1

END

You get the same result

Iterate through a list of strings in SQL and run SELECT for each string

If you got a concatenation of strings then the following approach would do the same instead of the row_by_row approach of looping through a cursor.

Much cleaner and performant.

create table t(x int, y varchar2(30));

insert into t 
select level,to_char(level)
  from dual
connect by level<=20;

--Passing a list of varchar as '12','9','5'
select *
  from t
where y in (select * 
              from TABLE(sys.odcivarchar2List('12','9','5'))
            )  

Iterate through a list of string in sql and assign as a variable

Declare @Id Integer
While exists (Select * From @Ids)
Begin      
    select fileName
    from MarketMessage as a LEFT JOIN MessageType310 as b ON a.MarketMessageID = b.MarketMessageID
    where b.MPRN = @id
End

This query is infinite because there are always rows in the table.

What you are trying to do is called cursors and you should avoid cursors in SQL when other alternative is possible. In this case OUTER APPLY will work.

First of all, import your excel file into database table.
See http://searchsqlserver.techtarget.com/feature/The-SQL-Server-Import-and-Export-Wizard-how-to-guide

Then you can use OUTER APPLY keyword like:

DECLARE @excel_list TABLE ( filter NVARCHAR(MAX) )

INSERT  INTO @excel_list
VALUES  ( 'one' ),
        ( 'two' ),
        ( 'three' )

SELECT  *
FROM    @excel_list AS excel
        OUTER APPLY ( SELECT    fileName
                      FROM      MarketMessage AS a
                                LEFT JOIN MessageType310 AS b ON a.MarketMessageID = b.MarketMessageID
                      WHERE     b.MPRN = excel.filter
                    ) o

Change @excel_list to imported table. This will return you all rows from excel table and also rows from right apply side where there will be match or NULLs where there will not be match. In APPLY query you can return scalar value as well as table.

T-SQL: Looping through an array of known values

declare @ids table(idx int identity(1,1), id int)

insert into @ids (id)
    select 4 union
    select 7 union
    select 12 union
    select 22 union
    select 19

declare @i int
declare @cnt int

select @i = min(idx) - 1, @cnt = max(idx) from @ids

while @i < @cnt
begin
     select @i = @i + 1

     declare @id = select id from @ids where idx = @i

     exec p_MyInnerProcedure @id
end

Iterate through a list to get strings in SQL

You could do a concat type query.

SELECT (Table.A + '_' + Table.B) AS A_B, COUNT(*) AS RowsCount FROM Table

I'm asuming the your table name is "Table", the result where you will find the strings you want would be the column named A_B, each record will have two things in each record, one would be the string you asked for, the other column would always be the same thing, the total number of records on you table.

The count part is kinda easy but check this link so you can use the specific count you need: http://www.w3schools.com/sql/sql_func_count.asp

---

Related Topics