Thanks for the inputs. I am interested in the fill method but that seems like a lot of work (any examples available)- If my UDT form has 150 entries for example, all I want is to select any column, and as many and as I want, and bind them to a datagrid or other objects:
This worked for me- the only problem was I had to change the dat type in the UDT to varchar max instaed of text:
O
/****** Object: StoredProcedure [dbo].[GetUDTData] Script Date: 06/21/2007 11:25:55 ******/
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
Proc [dbo].[GetUDTData] @param varchar(4000), @moduleid varchar(50)
as
declare
@val VARCHAR(100)
declare
@result VARCHAR(4000)
declare
declare
declare
@count int @count1 int @input varchar(4000)
SELECT
@result = 'SELECT userdefinedrowid,'
select
@count=0
select
@count1=0
select
@input =@param
WHILE
LEN( @param ) > 0 BEGIN select @count =@count + 1IF CHARINDEX( ',', @param ) > 0SELECT @val = LEFT( @param, CHARINDEX( ',', @param ) - 1 ) ,
@param
set
= RIGHT( @param, LEN( @param ) - CHARINDEX( ',', @param ) )ELSESELECT @val = @param, @param = SPACE(0)END @param=@input
WHILE
LEN( @param ) > 0 BEGIN select @count1 =@count1 + 1IF CHARINDEX( ',', @param ) > 0SELECT @val = LEFT( @param, CHARINDEX( ',', @param ) - 1 ) ,
@param
= RIGHT( @param, LEN( @param ) - CHARINDEX( ',', @param ) )ELSESELECT @val = @param, @param = SPACE(0)if @count1 < @countSET @result = @result + 'coalesce(min(case when FieldTitle = ' + '''' + @val + '''' +
' then FieldValue end) ,'''')' + ' As ' + '''' + @val + ''','
else
SET @result = @result + 'coalesce(min(case when FieldTitle = ' + '''' + @val + '''' +
' then FieldValue end) ,'''')' + ' As ' + '''' + @val + ''''
END
SELECT @result=@result + ' FROM UserDefinedData AS D INNER JOIN UserDefinedFields AS'SELECT @result=@result + ' F on D.UserDefinedFieldId = F.UserDefinedFieldId WHERE F.ModuleId =' + @moduleid + ' group by userdefinedrowid'
exec ( @result)
--print @count1