Thanks for the lead. It took me a second (with profiler) to figure out which stored procedure it was because the naming isn't tied in with dynamic user directory. Then I worried it was used by other modules, but it doesn't appear to be.
In case this helps anyone, or anyone has comments on how I did this, here is my new stored proc. I found that if I tried dynamic sorting on one of the right join columns (like MaidenName) the performance was terrible, so I run the dynamic SQL the stored proc already creates into a temp table and select and order by from there. I added one column which is populated by a CASE function between Maiden and Last.
Also, I found that I had to do all of my ordering in the stored proc, and then order by a null column in the module. If you order by a single column in the module, it completely overrides any ordering done in the stored proc.
All of my changes (inserted lines) are wrapped in
--RNCI
--
CREATE Procedure dbo.[DataSprings_RetrieveDNNUserProfileData]
@PortalID int,
@RoleFilter nvarchar(1000),
@LimitResults nvarchar(1000)
AS
Declare curDS Cursor
FOR
SELECT PropertyName, PropertyDefinitionID
FROM dbo.[ProfilePropertyDefinition]
where Visible=1
AND PortalID = @PortalID
AND Deleted=0
AND PRopertyName <> 'FirstName'
AND PropertyName <> 'LastName'
ORDER BY PropertyCategory, Vieworder
FOR READ ONLY;
Declare @PropertyName VarChar(500);
Declare @PropertyDefinitionID Integer;
Declare @SQLToUse VarChar(8000);
Declare @SQLToUseJoin VarChar(8000);
Declare @SQLToExecute Varchar(8000);
Declare @Alias VarChar(10);
-- RNCI
DECLARE @maidenNameAlias varchar(10);
DECLARE @tempTable nvarchar(4000);
--
Declare @intCounter Int;
set @PropertyName = '';
set @SQLToUse = '';
set @intCounter = 0;
set @Alias = '';
set @SQLToUseJoin = '';
OPEN curDS;
FETCH NEXT FROM CurDS INTO @PropertyName, @PropertyDefinitionID;
WHILE @@FETCH_STATUS = 0
BEGIN
set @intCounter = @intCounter + 1;
set @Alias = '[RJoin_' + CAST(@intCounter as varchar(20)) + ']';
if DataLength(@SQLToUse) = 0
BEGIN
set @SQLToUse = 'SELECT ' + @LimitResults + ' a.UserID, A.UserName, A.DisplayName, A.Email, A.FirstName, A.LastName, IsNull(' + @Alias + '.PropertyValue,''N/A'') as [' + @PropertyName + '] '
set @SQLToUseJoin = '
From (
SELECT distinct Y.UserID, B.UserName, B.DisplayNAme, B.Email, B.FirstNAme, B.LastName
from dbo.[UserProfile] As Y
inner join ProfilePropertyDefinition as Z on (Y.PropertyDefinitionID = Z.PropertyDefinitionID)
Left Outer Join dbo.[Users] B on B.UserID = Y.UserID
where Z.PortalID = ' + Cast(@PortalID as varchar(20)) + '
) As A
Left Outer join dbo.[UserProfile] as ' + @Alias + ' on (A.UserID = ' + @Alias + '.UserID and ''' + Cast(@PropertyDefinitionID as varchar(50))+ ''' = ' + @Alias + '.PropertyDefinitionID)
'
-- RNCI
SET @tempTable = 'CREATE TABLE #tempData ( UserID int, UserName nvarchar(100), DisplayName nvarchar(128), Email nvarchar(256), FirstName nvarchar(50), LastName nvarchar(50), ' + @PropertyName + ' nvarchar(3750) '
--
END
ELSE
BEGIN
-- RNCI
SET @tempTable = @tempTable + ', ' + @PropertyName + ' nvarchar(3750) '
IF @PropertyName = 'MaidenName' SET @maidenNameAlias = @alias
--
set @SQLToUse = @SQLToUse + ', ' + @Alias + '.PropertyValue as [' + @PropertyName + '] '
set @SQLToUseJoin = @SQLToUseJoin + 'Left Outer join dbo.[UserProfile] as ' + @Alias + ' on (A.UserID = ' + @Alias + '.UserID and ''' + Cast(@PropertyDefinitionID as varchar(50))+ ''' = ' + @Alias + '.PropertyDefinitionID)
'
END;
FETCH NEXT FROM CurDS INTO @PropertyName, @PropertyDefinitionID;
END
CLOSE CurDS;
DEALLOCATE CurDS;
-- RNCI
SET @SQLToUse = @SQLToUse + ', CASE ISNULL(' + @maidenNameAlias + '.PropertyValue, '''') WHEN '''' THEN LastName ELSE ' + @maidenNameAlias + '.PropertyValue END '
SET @tempTable = @tempTable + ', LastAtAHS varchar(500))'
--
--set @SQLToExecute = @SQLToUse + @SQLToUseJoin + @RoleFilter
Set @SQLToExecute = Convert(varchar(8000), @SQLToUse) + Convert(varchar(8000), @SQLToUseJoin) + Convert(varchar(8000), @RoleFilter)
-- RNCI
SET @SQLToExecute = REPLACE(@SQLToExecute, '''', '''''')
exec (@tempTable + '; INSERT INTO #tempData EXEC(''' + @SQLToExecute + '''); SELECT * FROM #tempData ORDER BY LastAtAHS, FirstName')
--
--print @SQLToExecute
--EXEC(@SQLToExecute);
GO |