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

Question

I have a list of strings which I would like to iterate and set as a variable in sql, I want to iterate a list of these strings ["one","two","three","four"] and then set each of these strings as a variable for a query. I have tried the following:

Declare @Ids Table (id integer primary Key not null)
Insert @Ids(id) values(81570517139)
Insert @Ids(id) values(81676666808)
Insert @Ids(id) values(81496685880)
Insert @Ids(id) values(81596620831)
Insert @Ids(id) values(81254409851)
Insert @Ids(id) values(81641592011)
Insert @Ids(id) values(81690996037)
Insert @Ids(id) values(81121685039)
Insert @Ids(id) values(81527008494)
Insert @Ids(id) values(81174933036)
Insert @Ids(id) values(81081564187)

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

But I am getting 'Arithmetic overflow error converting expression to data type int

Your question is not clear. Do you have multiple values in a single column or does this query return 4 rows? You need to explain your tables and what are trying to do. — Sean Lange
– Sean Lange, Commented Jan 27, 2015 at 16:25
Do you maybe mean you want to run the query multiple times, filtering it by each of your strings? — Andrew
– Andrew, Commented Jan 27, 2015 at 16:28
Does this help http://stackoverflow.com/questions/1589214/t-sql-looping-through-an-array-of-known-values — Sourav 'Abhi' Mitra
– Sourav 'Abhi' Mitra, Commented Jan 27, 2015 at 16:29
I actually have the 700 values in an excel spreadsheet column, I want to check each of the values in the column against my query and output as a list of the file associated if there is one — Jay
– Jay, Commented Jan 27, 2015 at 16:38

Giorgi Nakeuri · Accepted Answer · 2015-01-27 17:19:57Z

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.

Are you aware if this would affect database context in regards to migrations by adding this table temporarily?

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related