SPS不希望直接对数据库进行操作,当时是为了得到UserProfileData时所用的一些存储过程。 sp_helptext sps_sec_rGetPermInfo sp_helptext fn_sps_sec_GetDeltaFromPortalPerm sp_helptext profile_GetUserProfileData
exec dbo.profile_GetProfilePropertyInfo exec dbo.profile_UpdateUserProfileData @UpdatePropertyList = N'
'
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