SQL Loop

ta5 · 2012-09-14

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

ta5 · 2012-09-14

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

Howdy, Stranger!

Quick Links

Categories

SQL Loop

Answers