通过sql server得到SPS中的userprofile

来源：互联网发布：cydia 网络超时编辑：程序博客网时间：2024/04/28 10:47

SPS不希望直接对数据库进行操作，当时是为了得到UserProfileData时所用的一些存储过程。

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
) T
where T.RecordID = '10'

select * from UserProfileValue where PropertyVal='域/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

写了存储过程去SPS中的的存储过程：

————————————————————————
CREATE       PROCEDURE acf_getUserProFile
                        (
             @inputRowCountStart int=1,
             @inputRowCountEnd int=10,
             @inputWildSearch bit=1,
                         @inputAccountName bit=0,
                         @inputPreferredName bit=0,
                         @inputEmail bit=0,
                         @inputSearchString nvarchar(250),
             @P1 int output
                        )
AS
/*
**************************************************************************
输入：
要求：
返回：
**************************************************************************
*/
--declare @P1 int,@inputSearchString char(20)
--set @P1=52
--set @inputSearchString=rtrim(@SearchString)

if @inputAccountName=1
begin
exec acf_profile_SearchUser1 @RowCountStart = @inputRowCountStart, @RowCountEnd = @inputRowCountEnd, @WildSearch =@inputWildSearch, @Collation = N'Chinese_PRC_CI_AI', @bActiveOnly = 1, @TotalRowCount = @P1 output, @AccountName = 1, @SearchString = @inputSearchString
select @P1
end

if @inputPreferredName=1
begin
exec 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 @P1
end

if @inputEmail=1
begin
exec 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 @P1
end
GO

————————————————————————
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 OUTPUT

SET NOCOUNT ON

DECLARE @PROPERTY_ACCOUNTNAME_ID bigint SET @PROPERTY_ACCOUNTNAME_ID = 3 -- PropertyID
DECLARE @PROPERTY_PREFERREDNAME_ID bigint SET @PROPERTY_PREFERREDNAME_ID = 7 -- PropertyID
DECLARE @PROPERTY_EMAIL_ID bigint SET @PROPERTY_EMAIL_ID = 9 -- PropertyID
DECLARE @PROPERTY_DEPARTMENT_ID bigint SET @PROPERTY_DEPARTMENT_ID = 14 -- PropertyID
DECLARE @PROPERTY_TITLE_ID bigint SET @PROPERTY_TITLE_ID = 13 -- PropertyID
DECLARE @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,
@DoSort bit,
@SearchStringLen int,
@bDeleted bit

SET @ResultCount = 0
SET @DoSort = 0
IF @bActiveOnly = 1
SET @bDeleted = 0
ELSE
SET @bDeleted = 1

SELECT @SearchString = REPLACE(@SearchString, '''', '''''')

SELECT @SearchStringLen = LEN( LTRIM(@SearchString))
CREATE TABLE #TEMP
(
ID int IDENTITY (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 '
ELSE
BEGIN
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''' '
END

SELECT @Condition = @Condition + ' AND bDeleted = @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 @DoGeneralSearch
IF 0 = @TotalRowCount
BEGIN
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 bDeleted = ' + CAST( @bDeleted as nvarchar)
ELSE
SELECT @Condition = ' WHERE NTNAME = N''' + @SearchString + N''' ' + ' OR EMAIL = N''' + @SearchString + N''' AND bDeleted = ' + 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 bDeleted = ' + CAST( @bDeleted as nvarchar)
ELSE
SELECT @Condition = ' WHERE CONVERT(nvarchar(400), PropertyVal) = N''' + @SearchString + N''' AND bDeleted = ' + 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, @bDeleted

SELECT @TotalRowCount = COUNT(ID) FROM #Temp
SELECT @DoSort = 1

END
END
ELSE
BEGIN

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 INTO @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_ID

UPDATE #Temp SET Title = CAST(PropertyVal AS NVARCHAR(400))
            FROM #TEMP T
            INNER JOIN UserProfileValue U
            ON T.RecordID = U.RecordID
            WHERE U.PropertyID = @PROPERTY_TITLE_ID

IF @DoSort = 1
SELECT * FROM #Temp ORDER BY RecordID
ELSE
SELECT * FROM #Temp

SET NOCOUNT OFF
GO

——————————————————————————————
CREATE PROCEDURE acf_profile_SearchUser1
@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 OUTPUT

SET NOCOUNT ON

SET @ResultCount = 0
SET @DoSort = 0
IF @bActiveOnly = 1
SET @bDeleted = 0
ELSE
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 @Condition = @Condition + ' AND bDeleted = @bDeleted '
END

SELECT @Stmt =@Stmt + @Condition
SELECT @Param = N'@TotalRowCount int OUTPUT, @bDeleted bit'
EXEC SP_EXECUTESQL @Stmt, @Param, @TotalRowCount OUTPUT, @bDeleted

SELECT @Stmt = N'INSERT INTO #Temp(RecordID, UserID, AccountName, PreferredName, Email ) SELECT RecordID, UserID, NTNAME, PREFERREDNAME, EMAIL FROM UserProfile' + @Condition
EXECUTE(@Stmt)

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, @bDeleted

SELECT @TotalRowCount = COUNT(ID) FROM #Temp
SELECT @DoSort = 1

END
END
ELSE
BEGIN

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 Company = CAST(PropertyVal AS NVARCHAR(400))
            FROM #TEMP T
            INNER JOIN UserProfileValue U
            ON T.RecordID = U.RecordID
            WHERE U.PropertyID = @PROPERTY_Company_ID

IF @DoSort = 1
SELECT * FROM #Temp ORDER BY RecordID
ELSE
SELECT * FROM #Temp

SET NOCOUNT OFF
GO