Well... I have been thinking about this for a while... how best to support UDT in SQLGridSelectedView. My current belief is I shouldn't as I can't know all the possible needs.
So I decided to develop a generic Stored Procedure that can be tailored for most users if it is not correct out of the box. Try the following procedure and see if that will do the trick. You can install the procedure via the Host>SQL page.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetUserDefinedDatabyModule]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetUserDefinedDatabyModule]
go
Create Procedure [dbo].[GetUserDefinedDatabyModule]
@ModuleID int,
@VisibleOnly bit = 0
as
-- exec GetUserDefinedDatabyModule 440 -- default - show all
-- exec GetUserDefinedDatabyModule 440, 0 -- show all
-- exec GetUserDefinedDatabyModule 440, 1 -- show visible only
declare @FieldTitle varchar(50)
declare @FieldType varchar(20)
declare @FieldVisible bit
declare @CastTo varchar(100)
declare @crlf as char(2)
declare @SqlCmd varchar(8000)
Set @Crlf = char(13) + char(10)
Set @SqlCmd = 'Select ModuleID, UserDefinedRowId ' + @crlf
declare UDRFields Cursor
for select FieldTitle, FieldType, Visible
from dbo.UserDefinedFields
where ModuleID = @ModuleID
Order by FieldOrder
open UDRFields
fetch next from UDRFields into @FieldTitle, @FieldType, @FieldVisible
while (@@fetch_status <> -1)
begin
if @FieldVisible >= @VisibleOnly
begin
set @CastTo = (
Case @FieldType
when 'String' then 'Cast(UDD.[FieldValue] as varchar(100))'
when 'Boolean' then 'Cast(UDD.[FieldValue] as varchar(5))'
when 'Int32' then 'Cast(UDD.[FieldValue] as varchar(10))'
when 'Currency' then 'CAST(Cast(UDD.[FieldValue] as varchar(20)) AS decimal(17,2))'
when 'Date' then 'CAST(Cast(UDD.[FieldValue] as varchar(20)) AS datetime)'
when 'CreatedBy' then 'Cast(UDD.[FieldValue] as varchar(50))'
when 'CreatedAt' then 'CAST(Cast(UDD.[FieldValue] as varchar(20)) AS datetime)'
when 'ChangedBy' then 'Cast(UDD.[FieldValue] as varchar(50))'
when 'ChangedAt' then 'CAST(Cast(UDD.[FieldValue] as varchar(20)) AS datetime)'
else 'Cast(UDD.[FieldValue] as varchar(50))'
end)
Set @SqlCmd = @SqlCmd + ',(SELECT TOP 1 ' + @CastTo + ' ' + @crlf
Set @SqlCmd = @SqlCmd + ' FROM UserDefinedData UDD with (nolock), userdefinedfields UDF with (nolock)' + @crlf
Set @SqlCmd = @SqlCmd + ' WHERE UDD.[UserDefinedRowId] = udr.[UserDefinedRowId]' + @crlf
Set @SqlCmd = @SqlCmd + ' AND UDD.[UserDefinedFieldId] = udf.[UserDefinedFieldId]' + @crlf
Set @SqlCmd = @SqlCmd + ' AND UDF.[ModuleId] = udr.[ModuleId]' + @crlf
Set @SqlCmd = @SqlCmd + ' AND UDF.[FieldTitle] = ''' + @FieldTitle + ''''
Set @SqlCmd = @SqlCmd + ') AS [' + @FieldTitle + '] ' + @crlf
end
fetch next from UDRFields into @FieldTitle, @FieldType, @FieldVisible
end
close UDRFields
deallocate UDRFields
Set @SqlCmd = @SqlCmd + 'FROM [userdefinedrows] UDR with (nolock) ' + @crlf
Set @SqlCmd = @SqlCmd + 'WHERE UDR.[moduleid] = ' + Cast(@ModuleID as varchar) + @crlf
Set @SqlCmd = @SqlCmd + 'ORDER BY udr.UserDefinedRowID' +@crlf
--Print len(@sqlcmd)
--Print @SqlCmd
Exec( @SQLcmd)
Return @@rowcount
Several limitation exist - the biggest is the 8000 character limit on the generated Select, however 10 to 15 field should be OK. Also the performance will be rather slow on large volume of data. I would suggest special handling in those cases. Also, I have limited the text (string) field to 100 characters -- this may need to be altered based on what you are trying to display.
I believe it's good start. Hope it helps.
Paul.