After working with UDT - User Defined Tables - which is now call Form and List in DNN V5.0+, I was looking for a way to convert the UDT into a conventional table. By convention table, I mean a table with multiple data column and a single row per data item. UDT have simple structure that places one field per data item on a row, so if you have 10 data fields then the UDT will have 10 rows per data item. Compounding the issue is the there is only one UserDefinedTableData table.
Having developed a strong reporting module - SQLGridSelectedView (aka SGSV) - I wanted to be able to access the data in a UDT. To do this I created a stored procedure that would generate the appropriate T-SQL and then execute it to return the flatten data. The flatten data has one row per data item and a column for each data field. So, instead of 10 rows per data item I have one row with 10 columns.
The stored procudure expects the ModuleID of the corresponding Forms and List (or UDT) module. The first optional parameter is the bit field that when set to 1 will return just the generated T-SQL. The default is to have the procedured execute the T-SQL and return the result set. The second parameter defines a prefix that is prepended to each table reference. The default is "dbo.", but could be the '{databaseowner}{objectqualifier}' substitution values supported by SGSV and the Host/SQL modules.
To determine the appropriate Form and List module use the following select in Host/SQL module to determine which ModuleID to use: (Note: I have not used the '{databaseowner}{objectqualifier}' in front of the table names)
Select ModuleID, ModuleTitle from TabModules where ModuleID in (
Select ModuleID from Modules where ModuleDefID in (
Select ModuleDefID from dbo.ModuleDefinitions where DesktopModuleID in (
Select [DesktopModuleID] from [dbo].[DesktopModules]
where FriendlyName = 'Form and List')))
The procedure is written for SQL2000 as my production environment is still SQL2000 (ugh!). Also I not used the '{databaseowner}{objectqualifier}' substitution values when defining the procedure.
Update: Based on Mark's comments I have revised the script to use nvarchar(1000) and have also supported Email field type.
Create Procedure dbo.GetUDTData
@ModuleID int,
@ReturnSelect bit = 0,
@Prefix varchar(100) = 'dbo.'
as
-- Revision History
-- 03-sep-2010 Added @CastFieldValue to allow easy change to way FieldValue is Cast
-- - could use nvarchar(max) in SQL2005+
-- exec dbo.GetUDTData 418
-- exec dbo.GetUDTData 418, 1
-- exec dbo.GetUDTData 418, 0, '{databaseowner}{objectqualifier}'
DECLARE @UserDefinedFieldId int
DECLARE @FieldTitle varchar(50)
DECLARE @FieldType varchar(20)
DECLARE @Visible bit
DECLARE @CastFieldValue varchar(100)
DECLARE @SQL Varchar(8000)
DECLARE @CRLF Char(2)
set nocount on
DECLARE GetFieldsCursor INSENSITIVE CURSOR
FOR
SELECT UserDefinedFieldId, FieldTitle, FieldType, Visible
from UserDefinedFields
where ModuleID = @ModuleID
and Visible = 1
and FieldType <> 'Expression'
order by FieldOrder
OPEN GetFieldsCursor
FETCH NEXT FROM GetFieldsCursor INTO @UserDefinedFieldId, @FieldTitle, @FieldType, @Visible
Set @CRLF = CHAR(13) + CHAR(10)
Set @CastFieldValue = 'Cast(FieldValue as nvarchar(1000))'
Set @SQL = 'Select UserDefinedRowId as RowID' + @CRLF
WHILE @@FETCH_STATUS = 0
BEGIN
Set @SQL = @SQL + ' ,(Select '
Set @SQL = @SQL +
Case @FieldType
when 'Decimal' then 'Cast(' + @CastFieldValue + ' as Decimal)'
when 'Date' then 'Cast(' + @CastFieldValue + ' as DateTime)'
when 'DateTime' then 'Cast(' + @CastFieldValue + ' as DateTime)'
when 'CreatedAt' then 'Cast(' + @CastFieldValue + ' as DateTime)'
when 'ChangedAt' then 'Cast(' + @CastFieldValue + ' as DateTime)'
when 'Email' then '''mailto:'' + ' + @CastFieldValue + ' + ''">'' + ' + @CastFieldValue + ' + '''''
when 'Url' then ''''' + ' + @CastFieldValue + ' + '''''
else '' + @CastFieldValue + ''
End
Set @SQL = @SQL + ' from ' + @Prefix + 'UserDefinedData where UserDefinedFieldId = '
Set @SQL = @SQL + CAST(@UserDefinedFieldId as varchar) + ' '
Set @SQL = @SQL + 'and UserDefinedRowId = UDR.UserDefinedRowId'
Set @SQL = @SQL + ') as [' + @FieldTitle + ']' + @CRLF
FETCH NEXT FROM GetFieldsCursor INTO @UserDefinedFieldId, @FieldTitle, @FieldType, @Visible
END
Set @SQL = @SQL + 'from ' + @Prefix + 'UserDefinedRows UDR where ModuleId = '
Set @SQL = @SQL + CAST(@ModuleID as varchar) + @CRLF
CLOSE GetFieldsCursor
DEALLOCATE GetFieldsCursor
if @ReturnSelect = 1
begin
Print @SQl
Select @SQL
end
else
execute(@SQL)
return @@rowcount
GO
The stored procedure has some limitation, specifically, it does not support expression. This may not be an issue as the user can take the generated T-SQL and revise it as necessary to generate the calculated column.
For simple example, Assume module 390 is a UDT with the structure of
| EngineID |
Text |
| EngineType |
Text |
| Manufacturer |
Text |
| PurchaseDate |
Date |
| Cost |
Decimal |
| Website |
Url |
The typical execution of the procedure would something like
exec GetUDTData 390
which would generate and execute the follow T-SQL:
Select UserDefinedRowId as RowID
,(Select Cast(FieldValue as varchar) from dbo.UserDefinedData where UserDefinedFieldId = 5 and UserDefinedRowId = UDR.UserDefinedRowId) as [EngineID]
,(Select Cast(FieldValue as varchar) from dbo.UserDefinedData where UserDefinedFieldId = 6 and UserDefinedRowId = UDR.UserDefinedRowId) as [EngineType]
,(Select Cast(FieldValue as varchar) from dbo.UserDefinedData where UserDefinedFieldId = 7 and UserDefinedRowId = UDR.UserDefinedRowId) as [Manufacturer]
,(Select Cast(Cast(FieldValue as varchar) as DateTime) from dbo.UserDefinedData where UserDefinedFieldId = 8 and UserDefinedRowId = UDR.UserDefinedRowId) as [PurchaseDate]
,(Select Cast(Cast(FieldValue as varchar) as Decimal) from dbo.UserDefinedData where UserDefinedFieldId = 9 and UserDefinedRowId = UDR.UserDefinedRowId) as [Cost]
,(Select '' + Cast(FieldValue as varchar) + '' from dbo.UserDefinedData where UserDefinedFieldId = 11 and UserDefinedRowId = UDR.UserDefinedRowId) as [website]
from dbo.UserDefinedRows UDR where ModuleId = 390
You can either use the stored procedure or the T-SQL generated by the stored procedure in whatever report module you are using (such as SGSV).
Hope this Helps
Paul.