SQL Loop

ta5
ta5 Member Posts: 1,164
edited 2012-09-14 in SQL General
Hello
I try to select a value from a NAV table through SQL script. This is not very complicated, but I try to do that dynamically for every company in the db. Has anybody done something like this and would share parts of the code (loop and variable table name of a given table because of "CompanyName$TableName")?

Thx in advance
Thomas

Answers

  • ta5
    ta5 Member Posts: 1,164
    After having a good lunch...this is what I was looking for...
    Regards
    Thomas
    USE test_db
GO

DECLARE @compname VARCHAR(50)
DECLARE @sqlCommand varchar(1000) 
DECLARE company_cursor CURSOR FOR SELECT Name FROM Company

OPEN company_cursor
FETCH NEXT FROM company_cursor into @compname;
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @sqlCommand = 'SELECT * from [' + @compname + '$Company Information]'
   EXEC (@sqlCommand) 
   FETCH NEXT FROM company_cursor;
END

CLOSE company_cursor;
DEALLOCATE company_cursor;
GO

Categories