How to do foreach select in single SQL query

Question

I have a problem, i try to do a foreach to select multi row and do a select in a each one.

This is my table :

| TRANSLATION_ID    | TABLE_NAME                | COLUMN_NAME   | ID_COLUMN     | CODELANGUE_COLUMN     |
|----------------   |-------------------------  |-------------  |-----------    |-------------------    |
| 1                 | CHAMPS_LANGUE             | VALUE1        | ZZZ           | CODELANGUE            |
| 2                 | DELAIS_LIBELLES           | VALUE2        | YYY           | CODELANGUE            |
| 3                 | MODELES_ENQUETES_LIGNES   | VALUE3        | XXX           | CODELANGUE            |
| ...               | ...                       | ...           | ...           |                       |

And I would like to do multiple SELECT on every TABLE like CHAMPS_LANGUE , DELAIS_LIBELLES etc..

FOREACH TABLE_NAME
 SELECT COLUM_NAME, ID_COLUM FROM TABLE_NAME WHERE CODELANGUE = 1

Let me know if a not very explicit. :)

You need create a dynamic query, You **can't** use a field as `Tablename` — Juan Carlos Oropeza, Sep 21 '15 at 14:47
If you want to stay in Sql-Server land you'll need a stored procedure and dynamically generate the SQL based off of this table. You could also hit the database from an outside script or code that would that would dynamically generate and execute the SQL based on this table. — JNevill, Sep 21 '15 at 14:48
It's juste a One shoot script, for an export in the end. So yes I think I have to do an outside script, but i'm not familiar with SQL — sbouaked, Sep 21 '15 at 14:54
This does what you need it to, just change the UPDATE statement to a SELECT http://stackoverflow.com/questions/8350397/loops-within-dynamic-sql — twoleggedhorse, Sep 21 '15 at 14:55

score 2 · Accepted Answer · edited May 23 '17 at 12:23

Modified an answer by @legendofawesomeness found here to make it more relevant for your situation

--Cursor for iterating
DECLARE @tableCursor       CURSOR
DECLARE @TABLE_NAME        NVARCHAR(255)
DECLARE @COLUM_NAME        NVARCHAR(255)
DECLARE @ID_COLUM          NVARCHAR(255)
DECLARE @CODELANGUE_COLUMN  NVARCHAR(255)

SET @tableCursor = CURSOR FOR SELECT TABLE_NAME, COLUM_NAME, ID_COLUMN, CODELANGUE_COLUMN FROM [tableData] -- Substitute with the name of your table as given in your question (which you didn't specify)

OPEN @tableCursor
FETCH NEXT FROM @tableCursor INTO @TABLE_NAME, @COLUM_NAME, @ID_COLUMN, @CODELANGUE_COLUMN
WHILE (@@fetch_status = 0)
BEGIN
   --dynamic sql
   DECLARE @sql NVARCHAR(max)

   --Your logic here...
   SET @sql = 'SELECT ' + @COLUM_NAME + ', ' + @ID_COLUM + ' FROM ' + @TABLE_NAME + ' WHERE ' + @CODELANGUE_COLUMN + ' = 1'
   EXEC dbo.sp_executesql @sql

FETCH NEXT FROM @tableCursor INTO @TABLE_NAME, @COLUM_NAME, @ID_COLUMN, @CODELANGUE_COLUMN
END

CLOSE @tableCursor
DEALLOCATE @tableCursor

This is it ! But I have an error with `Could not find stored procedure 'SELECT VALEUR, CODECHAMPLISTE FROM bdc_CHAMPS_LISTES_LANGUE WHERE CODELANGUE = 1;'.l` — sbouaked, Sep 21 '15 at 15:24
How it's because I have to use `EXEC dbo.sp_executesql @sql` instead ! Thx @twoleggedhorse ! — sbouaked, Sep 21 '15 at 15:26
@sbouaked I have updated the answer with dbo.sp_executesql for future reference — twoleggedhorse, Sep 22 '15 at 11:21

How to do foreach select in single SQL query

1 Answers1