Athlinks profile health check

ProfileHealthCheck.sql

DECLARE @login nVARCHAR(50)

SET @login = ''

SET NOCOUNT ON

IF @login = '' 
BEGIN
	RAISERROR ('Please define @login', 18, 1)
	RETURN
END

-- Check if ASP.NET user exists

DECLARE @uid UNIQUEIDENTIFIER

SET @uid = (SELECT au.UserId
              FROM aspnet_Users AS au WHERE au.UserName LIKE @login)
              
SELECT
	IIF(am.UserId IS NULL, 'Doesn''t exist', 'Exist') AS 'Membership',
	IIF(ap.UserId IS NULL, 'Doesn''t exist', 'Exist') AS 'Profile'
FROM aspnet_Users AS au
LEFT OUTER JOIN aspnet_Membership AS am ON am.UserId = au.UserId
LEFT OUTER JOIN aspnet_Profile AS ap ON ap.UserId = au.UserId
WHERE au.UserId = @uid

IF @uid IS NOT NULL
	BEGIN
		PRINT 'ASP.NET User exists'
		SELECT @uid AS 'UserID'
	END
ELSE
	BEGIN
		RAISERROR ('ASP.NET user does not exist', 18, 1)
		return
	END
	
-- Try to get RacerID from aspnet_Profile

DECLARE @propVal VARCHAR(12)
DECLARE @racerID INT

set @propVal = CONVERT( VARCHAR(12), (SELECT ap.PropertyValuesString FROM aspnet_Profile AS ap WHERE ap.UserId = @uid))
	
IF @propVal IS NULL
BEGIN
	RAISERROR ('RacerID can not be found in aspnet_Profile', 18, 1)
	RETURN
END
ELSE
	BEGIN
		SET @racerID = CONVERT(INT, @propVal)
		SELECT @racerID AS 'RacerID' 
	END
	
-- See if vr_t_RacerDetail entry is there

IF EXISTS(SELECT 1 FROM vr_t_RacerDetail AS vtrd WHERE vtrd.RCR_RacerID = @racerID)
	BEGIN
		SELECT 'Exists' AS 'RacerDetail entry'
	END
ELSE
	BEGIN
		SELECT 'Missing' AS 'RacerDetail entry'
	END

-- See if racer was merged
IF EXISTS(SELECT 1 FROM vr_t_RacerOld AS vtro WHERE vtro.RCR_RacerID = @racerID)
	BEGIN
		SELECT 
			'Yes' AS 'Racer Merged',
			(SELECT vtrd.RCR_RacerID FROM vr_t_RacerOld AS vtrd WHERE vtrd.RCR_RacerID = @racerID) AS 'New RacerID'	
	END
	ELSE
	BEGIN
		SELECT 
			'No' AS 'Racer Merged' 
	END

SET NOCOUNT ON