I've been looking for such a function for a long time without success. So I decided to formulate one. I am sure the sql experts will laugh at it but it works for me. Someone could improve on it by adding preliminary codes so that previous versions would be deleted before a new one is installed. For now, everything is manual. Here are the steps for using this query:
1. Select SQL from the host menu
2. Paste in the code
3. Check Run as Script
4. Click Execute
(This is done only once. Thereafter the function should be there and available)
If you did not get an error you are good to go.
Pitfalls to watch out for:
1. When calling the function you might need to call a specific 'database owner' since it may not default to dbo.
2. If you load it once even with errors, you may need to manually delete it before reloading it through SQL
I've included a sample query. Replace the xxx in xxx.jltGetProfileElement with your dbo.
CREATE FUNCTION jltGetProfileElement
(
@userID as int,
@ProfilePropertyName as nvarchar(100)
)
RETURNS nvarchar(4000) AS
BEGIN
-- If input is invalid, return null.
IF @ProfilePropertyName IS NULL
OR LEN(@ProfilePropertyName) = 0
OR @userID IS NULL
OR @userID < 1
RETURN NULL
DECLARE @PropertyValue AS NVARCHAR(400)
SET @PropertyValue =
(
SELECT UserProfile.PropertyValue
FROM (Users INNER JOIN UserProfile ON Users.UserID = UserProfile.UserID) INNER JOIN ProfilePropertyDefinition ON UserProfile.PropertyDefinitionID = ProfilePropertyDefinition.PropertyDefinitionID
WHERE (((Users.UserID)=@userID) AND ((ProfilePropertyDefinition.PropertyName)=@ProfilePropertyName))
)
RETURN @PropertyValue
END
Example:
The 2 required parameters are userID and the name of profile. The UserID is acquired from the query itself.
select userID,username,firstname,lastname,
xxx.jltGetProfileElement(users.userID,'Street') as Street,
xxx.jltGetProfileElement(users.userID,'City') as City,
xxx.jltGetProfileElement(users.userID,'Region') as Region,
xxx.jltGetProfileElement(users.userID,'PostalCode') as PostalCode
from users