Parse Microsoft Core Providers in SQL Server
December 21, 2014
SQL / Databases
The Microsoft Core Providers package provides useful built-in security and user-profile support for ASP.net and MVC applications. Given the flexibility of the Profile provider, Microsoft chose to store the data in SQL Server in a modified name/value pair system. This is not a problem if you are using the information only in your code. But sometimes, it is convenient to pull a piece or two of profile data as part of a T-SQL query. I faced this problem a few days ago and came up with the following solution.
I created a scalar user-defined function (UDF) that accepts the name of the property you want and returns it as an nvarchar.
CREATE FUNCTION dbo.[Profile_GetToken]
(
@property nvarchar(100),
@names nvarchar(MAX),
@values nvarchar(MAX)
)
RETURNS nvarchar(1000)
AS
BEGIN
DECLARE @ret nvarchar(1000)
IF COALESCE(@names,'') = '' OR COALESCE(@values, '') = ''
RETURN N''
-- see if property actually exists in the profile
DECLARE @propIdx int
SET @propIdx = CHARINDEX(@property, @names, 1)
IF @propIdx = 0
RETURN N''
-- get the property's starting and ending index
SET @ret = SUBSTRING(@names, @propIdx + LEN(@property) + 1, 2147483647)
DECLARE @startPos nvarchar(20), @endPos nvarchar(20), @tokenCount int, @lp int, @chr char(1)
DECLARE @token nvarchar(1000)
SET @tokenCount = 0
SET @lp = 1
SET @startPos = N''
SET @endPos = N''
WHILE @lp <= LEN(@ret)
BEGIN
SET @chr = SUBSTRING(@ret, @lp, 1)
IF @chr = N':'
BEGIN
SET @tokenCount = @tokenCount + 1
IF @tokenCount >= 2
BREAK
END
ELSE
BEGIN
IF @tokenCount = 0
SET @startPos = @startPos + @chr
ELSE
SET @endPos = @endPos + @chr
END
SET @lp = @lp + 1
END
IF CONVERT(int, @endPos) < 1
RETURN N''
SET @ret = SUBSTRING(@values, CONVERT(int, @startPos) + 1, CONVERT(int, @endPos))
RETURN @ret
END