Querying a Select Lookup Table

[anderson]

How can I query results of a particular lookup table from SoftPro Select?
SoftPro Select offers dynamic functionality relative to Lookup Tables including BASE/ALIAS functionality, Versioning, and other “Lookup Table” properties. Providing this functionality in the application without duplicating data in the database is done by dynamically building each lookup table. Each table is stored in the database separately. The name of a specific reference to that table and the specific column structure relative to that table can be obtained by cross referencing the LOOKUP table and three key data elements:
- NAME – this corresponds to the application NAME of a table, e.g. “LENDER”
- ID – a GUID associated with a particular named version of a table, e.g the “LENDER” app table is actually the “LKUP_GUID” table where GUID is replaced by the ID value of the LENDER table
- COLUMNS – an XML reference containing specific column related properties for a particular table, including a list of COLUMN names.
So, retrieving a list of entries for a particular lookup “table” from SoftPro Select requires a few steps:

1.Find the name of the physical table needed, e.g. LENDER in the example below:
EXAMPLE
select 'LKUP_' + REPLACE(CAST(L.ID AS VARCHAR(40)),'-','_') from
lookup l where name = 'lender'

2.Find the name of each column for that particular “table”, e.g.
EXAMPLE
SELECT col.element.value('@name','VARCHAR(128)') ColumnList, l.name
FROM dbo.[lookup] l cross apply l.columns.nodes ('declare
namespaces="http://schemas.softprocorp.com/spnet/lookups/20
09/10"; //s:Column') col(element)
WHERE l.name = 'lender'

3.Put it all together
EXAMPLE
--change the @LookupTableName to the name of the table you want data from. Line 11;
DECLARE @LookupTableName VARCHAR(128);
DECLARE @RealLookupTableName VARCHAR(128);
DECLARE @Loop INT;
DECLARE @LoopEnd INT;
DECLARE @SQL_Select NVARCHAR(3000);
DECLARE @SQL_From NVARCHAR(100);
DECLARE @Col_Name VARCHAR(128);
DECLARE @Col_As VARCHAR(128);

SET @LookupTableName = 'C - Lender';
SET @Loop = 1;

--Base tables only
IF (SELECT COUNT(*) FROM dbo.[Lookup] WHERE Name = @LookupTableName AND [Type] = 'B' ) = 1
BEGIN
CREATE TABLE #LkpCol (ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
TableName VARCHAR(128),
ColumnName VARCHAR(128),
ColumnPath VARCHAR(128),
ColumnID VARCHAR(128));

INSERT INTO #LkpCol
SELECT
L.Name AS LookupTableName,
col.value('@name', 'NVARCHAR(100)') AS ColumnName,
col.value('@path', 'NVARCHAR(100)') AS ColumnPath,
col.value('@id', 'NVARCHAR(100)') AS ColumnID
FROM
dbo.[Lookup] L
CROSS APPLY L.Columns.nodes('declare namespace s="http://schemas.softprocorp.com/spnet/lookups/2009/10"; //s:Column') AS c(col)
WHERE
L.Name = @LookupTableName;

SET @LoopEnd = @@RowCount;

--set ColumnID to real column name in table
UPDATE #LkpCol
SET ColumnID = '[v_' + replace(ColumnID,'-','_')+']',
ColumnName = REPLACE(ColumnName,' ','');

SELECT @RealLookupTableName = 'dbo.[lkup_'+REPLACE(CAST(L.ID AS VARCHAR(36)),'-','_')+']'
FROM dbo.[Lookup] L
WHERE Name = @LookupTableName;

--build SQL to select from lookup table
SET @SQL_Select = 'SELECT '
SET @SQL_From = 'FROM ' + @RealLookupTableName + ' ';

WHILE @Loop <= @LoopEnd
BEGIN
SELECT @Col_Name = ColumnID, @Col_As = ColumnName FROM #LkpCol WHERE ID = @Loop;
IF @Loop = 1
SET @SQL_Select = @SQL_Select + @Col_Name + ' AS [' + @Col_As + ']'
ELSE
SET @SQL_Select = @SQL_Select + ',' + @Col_Name + ' AS [' + @Col_As + ']'

SET @Loop = @Loop + 1;
END

SET @SQL_Select = @SQL_Select + ' ';

--PRINT (@SQL_Select + @SQL_From);
EXEC (@SQL_Select + @SQL_From);

DROP TABLE #LkpCol;
END
ELSE
SELECT 'Lookup Table [' + @LookupTableName + '] NOT Found';

[john.morton]

Thanks Sam.