Join Tables by a Field that contains a delimited string

ALTER proc [dbo].[sp_UserPermissionLoad]
(
@LoginUserId varchar(50),
@IsStudent int
)
as
--exec sp_UserPermissionLoad 'admin', 0
--exec sp_UserPermissionLoad T00133, 0
begin
if(@IsStudent = 0)
begin
declare @RoleId varchar(max),
@start INT, @end INT,
@tempROleId varchar(10)
declare @tblNumberGroups TABLE (FormConstant varchar(max),FormDescription varchar(max),LoginUserId varchar(30),UserId varchar(30),UserName varchar(max),RoleName varchar(max))
--DECLARE @ReleId VARCHAR(500)
--SELECT @RoleId=RoleId FROM T_UserSetup WHERE LoginUserId=@LoginUserId

--select PAPR.FormConstant,PAPR.FormDescription,US.LoginUserId,US.UserId,US.UserName,RM.RoleName
--from T_PermissionAssignasPerRole PAPR inner join
--T_RoleManagement RM on RM.RoleId = PAPR.RoleId inner join
--T_UserSetup US on US.RoleId = RM.RoleId
SELECT @RoleId = RoleId FROM T_UserSetup WHERE LoginUserId = @LoginUserId
SELECT @start = 1, @end = CHARINDEX(',', @RoleId)
WHILE @start < LEN(@RoleId) + 1
BEGIN
IF @end = 0
SET @end = LEN(@RoleId) + 1
set @tempROleId = SUBSTRING(@RoleId, @start, @end - @start)
select @tempROleId
insert into @tblNumberGroups
select PAPR.FormConstant,PAPR.FormDescription,US.LoginUserId,US.UserId,US.UserName,RM.RoleName
from T_PermissionAssignasPerRole PAPR inner join
T_RoleManagement RM on RM.RoleId = PAPR.RoleId inner join
T_UserSetup US on @tempROleId = RM.RoleId
where US.LoginUserId = @LoginUserId
SET @start = @end + 1
SET @end = CHARINDEX(',', @RoleId, @start)
END
select * from @tblNumberGroups
end
else
begin
select PAPR.FormConstant,PAPR.FormDescription,US.StudentId,US.StudentsName,RM.RoleName
from T_PermissionAssignasPerRole PAPR inner join
T_RoleManagement RM on RM.RoleId = PAPR.RoleId inner join
T_Std_General_Information US on US.RoleId = RM.RoleId
where US.StudentId = convert(int,@LoginUserId)
end
end

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s