Creating a view across all companies—part 2

In last week’s post I wrote about how you could use a couple of fields in the $ndo$dbproperty table to create a ConvertInvalidChars() function that would turn your NAV company name into the prefix for the table name as it is used in the SQL database. This week we’re going to write some SQL that will create a dynamic select statement that we can use to create our view.

What is Dynamic SQL?

Dynamic SQL is when we use programming code to build up our SQL Select statement in a text variable and then execute that statement. In the case of our all company view, we don’t know how many companies we have or what they’re called, so we’re going to need to write something that will read the companies in the database and generate the correct SQL statement for us.

Here a simple example that shows that you can build up your SQL statement as a string and then use the EXEC() command to execute it.

DECLARE @SQLCommand AS VARCHAR(4000)

SET @SQLCommand = 'SELECT Name FROM Company'

EXEC( @SQLCommand)

The results of running this SQL is exactly the same as typing SELECT Name FROM Company into a query window and pressing F5.

Going Loopy with Cursors

Now we need to build up our SQL statement by looping through each of the companies in the database. To do this we’re going to use a cursor. A cursor is good because it allows you to fetch data back from the database one row at a time and then do something with the results. The downside to cursors is that they are slow (and this is one of the reasons that the performance of NAV is not as good as it could be as it makes extensive use of cursors when fetching data from the server).

In addition to using a cursor, we’re going to use the UNION ALL statement that will join the results from our select statements together into a single dataset – this is useful since we are trying to create a combined view, which means we need a single result set. Here’s the code:

DECLARE @CompanyName AS VARCHAR(30)
DECLARE @SQLCommand AS VARCHAR(4000) = ''
DECLARE mycur CURSOR FOR SELECT     
                                    Name
                         FROM       
                                    Company;

OPEN mycur

FETCH mycur INTO @CompanyName

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @SQLCommand <> '' SET @SQLCommand += 'UNION ALL '
    
    SET @SQLCommand += 'SELECT [Company Name] = ''' + @CompanyName + ''', * FROM [' + dbo.ConvertInvalidChars( @CompanyName) + '$Vendor] '
    
    FETCH mycur INTO @CompanyName
END;

--EXEC(@SQLCommand)

PRINT @SQLCommand

CLOSE mycur

DEALLOCATE mycur

As you can see I’ve commented out my EXEC command and replaced it with a PRINT command so we can see the SQL that gets generated in the message window. Here’s the SQL code it generates (obviously it doesn’t generate all of the formatting, I just inserted that to make it easier to follow the code):

SELECT     
           [Company Name] = 'CRONUS Australia Pty. Ltd.',
           *
FROM       
           [CRONUS Australia Pty_ Ltd_$Vendor]
UNION ALL
SELECT     
           [Company Name] = 'CRONUS New Zealand Ltd.',
           *
FROM       
           [CRONUS New Zealand Ltd_$Vendor]

If you copied that SQL statement into a query window, you’d see a dataset that contains all of the fields from our Vendor table for each of the companies with a new first column called “Company Name”. Obviously you’d need to change this if you had an actual field called “Company Name” in your table.

Making the View

The only thing left to do now is put in the CREATE VIEW AS code to create our view. I’m going to call my view Company Vendor. Here’s the final SQL that will create my view.

DECLARE @CompanyName AS VARCHAR(30)
DECLARE @SQLCommand AS VARCHAR(4000) = ''
DECLARE mycur CURSOR FOR SELECT     
                                    Name
                         FROM       
                                    Company;

OPEN mycur

FETCH mycur INTO @CompanyName

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @SQLCommand <> '' SET @SQLCommand += 'UNION ALL '
    ELSE SET @SQLCommand += 'CREATE VIEW [Company Vendor] AS '
    
    SET @SQLCommand += 'SELECT [Company Name] = ''' + @CompanyName + ''', * FROM [' + dbo.ConvertInvalidChars( @CompanyName) + '$Vendor] '
    
    FETCH mycur INTO @CompanyName
END;

EXEC( @SQLCommand)

PRINT @SQLCommand

CLOSE mycur

DEALLOCATE mycur

Next week we'll look at the final steps of making this view available as a linked table in NAV and talk about some of the potential gotcha’s.


Related
Recommended