CRM 权限内可查看的记录数

CREATE FUNCTION dbo.fn_GetFilteredIdsSqlString
    (
      @SystemUserId VARCHAR(36) ,
      @ObjectTypeCode VARCHAR(10) ,
      @entityname VARCHAR(100)
    )
RETURNS NVARCHAR(max)
  AS 
  BEGIN
 
        DECLARE @sqlText NVARCHAR(4000)
        SET @sqlText = ' SELECT ' + @entityname + 'id id from ' + @entityname
            + ' JOIN SystemUserBase u ON ( u.SystemUserId = '''
            + @SystemUserId + ''' AND u.IsDisabled = 0 ) '
            + ' CROSS JOIN dbo.fn_GetMaxPrivilegeDepthMask_SQL('
            + @ObjectTypeCode + ',''' + @SystemUserId + ''' )  pdm '
            + ' WHERE   ( ' + ' pdm.PrivilegeDepthMask IS NOT NULL '
            + ' AND ( ' + ' [' + @entityname + '].OwnerId IN ( '
            + '  SELECT  OwnerId '
            + '  FROM    [dbo].[fn_GetOwnerIdsForFilteredView](u.SystemUserId,'
            + @ObjectTypeCode + ')  ) ' + '   OR EXISTS ( SELECT  1 '
            + '  WHERE   ( ( ( ( pdm.PrivilegeDepthMask & 0x4 ) != 0 )  OR ( ( pdm.PrivilegeDepthMask & 0x2 ) != 0 )  '
            + '  AND [' + @entityname + '].[OwningBusinessUnit] IN ( '
            + '                                           SELECT    BusinessUnitId  FROM      SystemUserBusinessUnitEntityMap    WITH ( NOLOCK )   WHERE     SystemUserId =  '''
            + @SystemUserId + '''  AND ObjectTypeCode =' + @ObjectTypeCode
            + ' )   ) '
            + ' OR (  ( ( pdm.PrivilegeDepthMask & 0x8 ) != 0 )  AND ['
            + @entityname + '].[OwningBusinessUnit] IS NOT NULL) ) ' + ' OR ['
            + @entityname + '].' + @entityname + 'id IN ( '
            + ' SELECT  ObjectId    FROM    [dbo].[fn_GetSharedRecordIdsForFilteredView]('''
            + @SystemUserId + '''  , ' + @ObjectTypeCode + ') ) ) ) )'

RETURN @sqlText

END

因为存在动态SQL,所以不好写成 表值函数.暂时只能这样了.