Register  Login  
  February 6, 2012  
Search_Blog Minimize
Print  

Blog_Archive Minimize
Print  

Blog    
View_Blog Minimize
Feb 11

Written by: host
Monday, February 11, 2008 8:21 PM 

Well... I have been thinking about this for a while.. and 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. 

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 500 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.

Tags:

1 comment(s) so far...

Re: Chinese Footware - and the removal of...

GameCoach Factory Outlet Onlineare usuallyCoach Factoryin By Coach Outlet Online on   Thursday, February 02, 2012 1:37 AM


Your name:
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Security Code
Enter the code shown above in the box below
Add Comment   Cancel 

Blog_List Maximize
Print  

  Home | Blog | Forum | Subscriptions | Free Modules | Videos   Page generated in 0.1252048 seconds.
  Copyright 2010 by TressleWorks   Terms Of Use | Privacy Statement