The SPS does not want to do directly to the database directly, and some of the stored procedures used when UserProfileData are obtained. sp_helptext sps_sec_rGetPermInfo sp_helptext fn_sps_sec_GetDeltaFromPortalPerm sp_helptext profile_GetUserProfileData exec dbo.profile_GetUserProfileData @UserID = NULL, @SID = 0x01050000000000051500000026761E2FEE94020707E53B2B79040000 exec dbo.profile_GetProfilePropertyInfo exec dbo.profile_UpdateUserProfileData @UpdatePropertyList = N '
"PropertyName =" AboutMe ">
'Select * from (SELECT U. *, P.PropertyName, P.PropertyURI, MultiValValue FROM UserProfileValue U INNER JOIN PropertyList P ON U.PropertyID = P.PropertyID LEFT JOIN MultivalType MT ON U.MultiValTypeID = MT.MultiValTypeID LEFT JOIN MultiValList M ON U.MultiValID = M.MultiValID) Twhere T.RecordID = '10' select * from UserProfileValue where PropertyVal = 'domain / wslu' PropertyID <> '1' and PropertyID <> '2' select * from UserProfileValue where RecordID = ' 10 'and (PropertyID <>' 1 'and propertyid <>' 2 ') EXEC GETUSERPROFILEVALUE EXEC MY_GETUSERPROFILEVALUE SELECT * from my_getuserprofilevalue_v
Write the stored procedure to the stored procedure in the SPS: ---------------------- Create Procedure ACF_GETUSERPROFILE (@InputrowCountStart Int = 1, @INPUTROUNTEND INT = 10, @INputWildSearch bit = 1, @INPUTACCOUNTNAME BIT = 0, @InputPreferRedname Bit = 0, @INPUTEMAIL bit = 0, @Inputsearchstring nvarchar (250), @ p1 int output) AS / *********** *********************************************************** ************** Enter: Requirements: Return: ************************************** *************************************************************************************************************************************************************************************************************************************************************** / - DECLARE @ P1 int, @ inputSearchString char (20) - set @ P1 = 52 - set @ inputSearchString = rtrim (@SearchString) if @ inputAccountName = 1 beginexec acf_profile_SearchUser1 @RowCountStart = @inputRowCountStart, @RowCountEnd = @inputRowCountEnd, @WildSearch = @INPUTWILDSEARCH, @Collation = N'CHINESE_PRC_CI_AI ', @bactiveonly = 1, @totalrowcount = @ P1 Output, @accountname = 1 , @Searchstring = @InputSearchStringSelect @ p1nd
if @ inputPreferredName = 1beginexec acf_profile_SearchUser1 @RowCountStart = @ inputRowCountStart, @RowCountEnd = @ inputRowCountEnd, @WildSearch = @inputWildSearch, @Collation = N'Chinese_PRC_CI_AI ', @bActiveOnly = 1, @TotalRowCount = @ P1 output, @AccountName = NULL, @ PreferRedname = 1, @searchstring = @InputSearchstring SELECT @ p1nd
if @ inputEmail = 1 beginexec acf_profile_SearchUser1 @RowCountStart = @ inputRowCountStart, @RowCountEnd = @inputRowCountEnd, @WildSearch = @ inputWildSearch, @Collation = N'Chinese_PRC_CI_AI ', @bActiveOnly = 1, @TotalRowCount = @ P1 output, @AccountName = NULL, @Email = 1, @SearchString = @inputSearchString select @ P1endGO ------------------------ CREATE PROCEDURE acf_profile_SearchUser @ SearchString nvarchar (250), @ RowCountStart int , @ RowCountEnd int, @AccountName bit, @ PreferredName bit = NULL, @ Email bit = NULL, @ WildSearch bit = 1, @DoGeneralSearch bit = 0, @ Collation nvarchar (60), @ bActiveOnly bit = 1, @ TotalRowCount int OUTPUTAS
Set nocount on
DECLARE @PROPERTY_ACCOUNTNAME_ID bigint SET @PROPERTY_ACCOUNTNAME_ID = 3 - PropertyIDDECLARE @PROPERTY_PREFERREDNAME_ID bigint SET @PROPERTY_PREFERREDNAME_ID = 7 - PropertyIDDECLARE @PROPERTY_EMAIL_ID bigint SET @PROPERTY_EMAIL_ID = 9 - PropertyIDDECLARE @PROPERTY_DEPARTMENT_ID bigint SET @PROPERTY_DEPARTMENT_ID = 14 - PropertyIDDECLARE @PROPERTY_TITLE_ID bigint SET @ Property_Title_ID = 13 - PropertyIdDeclare @Property_Username_ID Bigint Set @Property_UserName_ID = 17 - PropertyID
DECLARE @ Stmt nvarchar (4000), @ Condition nvarchar (1000), @ Param nvarchar (4000), @ PropertyID bigint, @ RecordID bigint, @ UserID uniqueidentifier, @NTNAME nvarchar (400), @ PreferredNameVal nvarchar (256), @ EmailVal NVARCHAR (256), @ resultcount int, @ SearchStringlen int, @ bdleted bit
Set @Resultcount = 0set @dosort = 0if @BactiveOnly = 1 set @BDELETED = 0ELSE set @BDELETED = 1SELECT @searchstring = replace (@searchstring, '' ',' '' '')
Select @searchstringlen = le (Ltrim (@searchstring)) Create Table #Temp (ID Int Id Id Id Intity (1, 1) Not NULL,
Accountname Nvarchar (400), PreferRedname NVARCHAR (256), Email Nvarchar (256), Department NVARCHAR (250), Title Nvarchar (150), RecordId Bigint, Userid UniqueIdentifier,)
SELECT @Stmt = N'SELECT @TotalRowCount = Count (RecordID) FROM UserProfile 'IF 0 = @SearchStringLen SELECT @Condition = N' WHERE bDeleted = @bDeleted 'ELSEBEGIN IF 1 = @AccountName BEGIN IF @WildSearch = 1 SELECT @Condition = 'WHERE NTNAME LIKE N' '' @SearchString N '%' '' ELSE SELECT @Condition = 'WHERE NTNAME = N' '' @SearchString N '' '' END IF 1 = @PreferredName BEGIN IF @WildSearch = 1 SELECT @Condition = 'WHERE PREFERREDNAME LIKE N' '' @SearchString N '%' '' ELSE SELECT @Condition = 'WHERE PREFERREDNAME = N' '' @SearchString N '' '' END IF 1 = @Email begin if @Wildsearch = 1 select @condition = 'Where email like n' '' @searchstring n '%' 'else select @Condition =' where email = n '' @searchstring n '' 'Endselect @condition = @condition ' and bdleted = @BDELETED 'end
Select @stmt = @ stmt @condition select @Param = n '@ TotalrowCount Int output, @BDELETED BIT'Exec sp_executesql @stmt, @param, @totalrowcount output, @BDELETED
- CHECK whether need to search for @DoGeneralSearchIF 0 = @TotalRowCountBEGIN IF 1 = @PreferredName AND 1 = @DoGeneralSearch BEGIN IF @WildSearch = 1 SELECT @Condition = 'WHERE NTNAME LIKE N' '' @SearchString N '%' '' 'Or Email Like N' '' @searchstring n '%' 'and bdleted =' Cast (@BDELETED AS NVARCHAR) ELSE SELECT @Condition = 'Where NtName = n' '' @searchstring N '' ' ' Or email = n '' ' @searchstring n' '' and bdleted = ' cast (@BDELETED AS NVARCHAR) SELECT @stmt = N'Insert Into #Temp (RecordID, UserId, Accountname, PreferRedname, Email) Select Recordid, UserId, NtName, PreferRedname, Email from UserProfile ' @condition execute (@stmt)
--Search for username if @wildsearch = 1 Select @condition = 'WHERE Convert (NVARCHAR (400), PropertyVal) Like N' '' @searchstring n '%' and bdleted = ' Cast (@BDELETED AS NVARCHAR ) Else select @condition = 'WHERE Convert (nvarchar (400), protyval) = n' '' @searchstring n '' 'and bdleted =' cast (@BDELETED AS NVARCHAR)
SELECT @condition = @condition 'and proftyid = @Property_Username_ID'
SELECT @Stmt = N'INSERT INTO #Temp (RecordID, UserID, AccountName, PreferredName, Email) SELECT U.RecordID, UserID, NTNAME, PreferredName, Email FROM UserProfile U INNER JOIN UserProfileValue UP ON U.RecordID = UP.RecordID ' @Condition SELECT @PARAM = N '@ PROPERTY_USERNAME_ID int, @bDeleted bit' EXEC sP_EXECUTESQL @Stmt, @PARAM, @PROPERTY_USERNAME_ID, @bDeletedSELECT @TotalRowCount = COUNT (ID) FROM #Temp SELECT @DoSort = 1
Endendelsebegin
SELECT @Stmt = N'DECLARE M SCROLL CURSOR FOR SELECT NTNAME, PREFERREDNAME, EMAIL FROM UserProfile ' @Condition N' ORDER BY NTNAME COLLATE ' @Collation N' OPEN M FETCH RELATIVE @RowCountStart FROM M INTO @NTNAME, @ PreferredNameVal, @EmailVal WHILE @@ FETCH_STATUS = 0 BEGIN INSERT INTO #TEMP (AccountName, PreferredName, Email) VALUES (@NTNAME, @PreferredNameVal, @EmailVal) SELECT @RowCountStart = @RowCountStart 1 IF @RowCountStart> @RowCountEnd BREAK FETCH NEXT From m @ntname, @preferrednameval, @emailval end close m deallocate m '
SELECT @PARAM = N '@ RowCountStart INT, @RowCountEnd INT, @RecordID INT, @UserID UNIQUEIDENTIFIER, @NTNAME NVARCHAR (400), @PreferredNameVal NVARCHAR (256), @EmailVal NVARCHAR (256), @bDeleted bit' - select @Stmt EXEC SP_EXECUTESQL @stmt, @Param, @RowcountStart, @RowCountend, @recordid, @userid, @ntname, @preferrednameval, @emailval, @BDELETED
End
UPDATE #Temp SET Department = CAST (PropertyVal AS NVARCHAR (400)) FROM #TEMP T INNER JOIN UserProfileValue U ON T.RecordID = U.RecordID WHERE U.PropertyID = @PROPERTY_DEPARTMENT_IDUPDATE #Temp SET Title = CAST (PropertyVal AS NVARCHAR (400 )) From #temp t inner join userprofilevalue u on t.recordid = u.recordid a u.propertyid = @Property_title_id
IF @dosort = 1 Select * from #Temp Order by Recordid else Select * from #temp
SET NOCOUNT OFFGO ----------------------------- Create Procedure ACF_PROFILE_SEARCHUSER1 @ SearchString NVARCHAR (250), @ RowCountStart Int, @ RowCountend Int , @Accountname bit = null, @ Email bit = null, @ @ Wildsearch bit = 1, @dogeneralsearch bit = 0, @ collation nvarchar (60), @bactiveonly bit = 1, @ TotalrowCount Int Output
AS
Set nocount on
DECLARE @PROPERTY_ACCOUNTNAME_ID bigint SET @PROPERTY_ACCOUNTNAME_ID = 3 - PropertyIDDECLARE @PROPERTY_PREFERREDNAME_ID bigint SET @PROPERTY_PREFERREDNAME_ID = 7 - PropertyIDDECLARE @PROPERTY_EMAIL_ID bigint SET @PROPERTY_EMAIL_ID = 9 - PropertyIDDECLARE @PROPERTY_DEPARTMENT_ID bigint SET @PROPERTY_DEPARTMENT_ID = 14 - PropertyIDDECLARE @PROPERTY_TITLE_ID bigint SET @ PROPERTY_TITLE_ID = 13 - PropertyIDDECLARE @PROPERTY_USERNAME_ID bigint SET @PROPERTY_USERNAME_ID = 17 - PropertyIDDECLARE @PROPERTY_Company_ID bigint SET @PROPERTY_Company_ID = 27 - PropertyID
DECLARE @ Stmt nvarchar (4000), @ Condition nvarchar (1000), @ Param nvarchar (4000), @ PropertyID bigint, @ RecordID bigint, @ UserID uniqueidentifier, @NTNAME nvarchar (400), @ PreferredNameVal nvarchar (256), @ EmailVal NVARCHAR (256), @ resultcount int, @ searchstringlen int, @ bdleted bitset @Resultcount = 0set @dosort = 0if @BactiveOnly = 1 set @BDELETED = 0ELSE set @bdeleted = 1
Select @searchstring = Replace (@searchstring, '' ',' '' '')
SELECT @SearchStringLen = LEN (LTRIM (@SearchString)) CREATE TABLE #TEMP (ID int IDENTITY (1, 1) NOT NULL, RecordID bigint, UserID uniqueidentifier, AccountName nvarchar (400), PreferredName nvarchar (256), Email nvarchar (256 ), Department NVARCHAR (250), Title Nvarchar (150), Company NVARCHAR (250))
SELECT @Stmt = N'SELECT @TotalRowCount = Count (RecordID) FROM UserProfile 'IF 0 = @SearchStringLen SELECT @Condition = N' WHERE bDeleted = @bDeleted 'ELSEBEGIN IF 1 = @AccountName BEGIN IF @WildSearch = 1 SELECT @Condition = 'WHERE NTNAME LIKE N' '' @SearchString N '%' '' ELSE SELECT @Condition = 'WHERE NTNAME = N' '' @SearchString N '' '' END IF 1 = @PreferredName BEGIN IF @WildSearch = 1 SELECT @Condition = 'WHERE PREFERREDNAME LIKE N' '' @SearchString N '%' '' ELSE SELECT @Condition = 'WHERE PREFERREDNAME = N' '' @SearchString N '' '' END IF 1 = @Email begin if @Wildsearch = 1 select @condition = 'Where email like n' '' @searchstring n '%' 'else select @Condition =' where email = n '' @searchstring n '' 'Endselect @condition = @condition ' and bdleted = @BDELETED 'end
Select @stmt = @ stmt @condition select @Param = n '@ TotalrowCount Int output, @BDELETED BIT'Exec sp_executesql @stmt, @param, @totalrowcount output, @BDELETED
- CHECK whether need to search for @DoGeneralSearchIF 0 = @TotalRowCountBEGIN IF 1 = @PreferredName AND 1 = @DoGeneralSearch BEGIN IF @WildSearch = 1 SELECT @Condition = 'WHERE NTNAME LIKE N' '' @SearchString N '%' '' 'Or Email Like N' '' @searchstring n '%' 'and bdleted =' Cast (@BDELETED AS NVARCHAR) ELSE SELECT @Condition = 'Where NtName = n' '' @searchstring N '' ' ' Or email = n '' ' @searchstring n' '' and bdleted = ' cast (@BDELETED AS NVARCHAR) SELECT @stmt = N'Insert Into #Temp (RecordID, UserId, Accountname, PreferRedname, Email) Select Recordid, UserId, NtName, PreferRedname, Email from UserProfile ' @condition execute (@stmt)
--Search for username if @wildsearch = 1 Select @condition = 'WHERE Convert (NVARCHAR (400), PropertyVal) Like N' '' @searchstring n '%' and bdleted = ' Cast (@BDELETED AS NVARCHAR ) Else select @condition = 'WHERE Convert (nvarchar (400), protyval) = n' '' @searchstring n '' 'and bdleted =' cast (@BDELETED AS NVARCHAR)
SELECT @Condition = @Condition 'AND PropertyID = @ PROPERTY_USERNAME_ID' SELECT @Stmt = N'INSERT INTO #Temp (RecordID, UserID, AccountName, PreferredName, Email) SELECT U.RecordID, UserID, NTNAME, PreferredName, Email FROM UserProfile U INNER JOIN UserProfileValue UP ON U.RecordID = UP.RecordID ' @Condition SELECT @PARAM = N' @ PROPERTY_USERNAME_ID int, @bDeleted bit 'EXEC sP_EXECUTESQL @Stmt, @PARAM, @PROPERTY_USERNAME_ID, @bDeletedSELECT @TotalRowCount = COUNT (ID) From #Temp Select @dosort = 1
Endendelsebegin
SELECT @Stmt = N'DECLARE M SCROLL CURSOR FOR SELECT RecordID, UserID, NTNAME, PREFERREDNAME, EMAIL FROM UserProfile ' @Condition N' ORDER BY NTNAME COLLATE ' @Collation N' OPEN M FETCH RELATIVE @RowCountStart FROM M INTO @RecordID, @UserID, @NTNAME, @PreferredNameVal, @EmailVal WHILE @@ FETCH_STATUS = 0 BEGIN INSERT INTO #TEMP (RecordID, UserID, AccountName, PreferredName, Email) VALUES (@RecordID, @UserID, @NTNAME, @PreferredNameVal, @EmailVal) SELECT @RowCountStart = @RowCountStart 1 IF @RowCountStart> @RowCountEnd BREAK FETCH NEXT FROM M INTO @RecordID, @UserID, @NTNAME, @PreferredNameVal, @EmailVal END CLOSE M DEALLOCATE M '
SELECT @PARAM = N '@ RowCountStart INT, @RowCountEnd INT, @RecordID INT, @UserID UNIQUEIDENTIFIER, @NTNAME NVARCHAR (400), @PreferredNameVal NVARCHAR (256), @EmailVal NVARCHAR (256), @bDeleted bit'EXEC SP_EXECUTESQL @ Stmt, @param, @RowcountStart, @Rowcountend, @recordid, @userid, @ntname, @preferrednameval, @emailval, @BDELETED
End
Update #Temp Set Department = CAST (PropertyVal As NVARCHAR (400)) from #temp Tinner Join UserProfileValue U on T.RecordidId = u.recordid WHERE U.PROPERTYID = @Property_Department_ID
Update #temp set title = cast (PropertyVal As NVARCHAR (400)) from #temp Tinner Join UserProfileValue U on T.RecordidId = u.recordidid Where u.propertyId = @Property_Title_ID
Update #Temp Set Company = CAST (PropertyVal As NVARCHAR (400)) from #temp T I on T.RecordidId = u.recordid WHERE U.PROPERTYID = @Property_company_ID
IF @dosort = 1 Select * from #Temp Order by Recordid else Select * from #temp
Set NoCount Offgo
