通过sql server得到SPS中的userprofile

原创 2005年02月28日 17:23:00
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'<?xml version="1.0" encoding="utf-16"?>

 
   
     

" 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

AS

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

AS

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

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

用java从sql server2008中读取得到的date时间数据比数据库里的实际时间要少两天

用Java做一个课程设计(要求数据库用sql server),有个数据项是“处理时间”,只需要日期不需要集体时分秒,在数据库定义时间类型为“date”。但是用Java从sql server读取出来的时...

sql server 得到连续日期查询

转载:http://blog.csdn.net/xys_777/article/details/5681208 --〉生成连续日期的方法 IF OBJECT_ID('tempdb..#t') ...
  • xxt1993
  • xxt1993
  • 2016年03月31日 13:40
  • 793

SQL Server 得到SPID,唯一的sessionID

SQL Server 得到SPID,唯一的sessionID 用来查看哪些sessionID正在执行什么操作

SQL Server 2005中的CTE递归查询得到一棵树

感觉这个CTE递归查询蛮好用的,先举个例子:   [c-sharp] view plain copy  print? use City;   go  ...

Sql server 得到表结构的Sql语句

declare @sql varchar(8000),@tablename varchar(100) set @tablename ='Test'--这里输入表名 set @sql = ' if ob...

sql server2005安装包

  • 2017年12月14日 14:15
  • 51B
  • 下载

SQL SERVER数据库备份与恢复

1 数据库备份      选择要备份的数据库,右键->tasks->Back Up 在Destination中选择Add,然后填写备份文件的路径和文件名,备份文件以.bak为后缀. ...
  • cqulyk
  • cqulyk
  • 2015年04月16日 23:48
  • 301

SQL Server JDBC_4 jar包

  • 2017年12月16日 08:40
  • 4.15MB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:通过sql server得到SPS中的userprofile
举报原因:
原因补充:

(最多只允许输入30个字)