declare @fspro_userId int = ; declare @dashboardPattern varchar(101) = ; declare @ownerId int = ; declare @ownerType varchar(3) = ; with sharedDashboards as ( select uds.ownerId, uds.ownerType, uds.fspro_userId as sourceFspro_userId, uds.dashboard as sourceDashboard, sd.lastUpdated sourceLastUpdated, sd.dashboardName sourceDashboardName from tbl_STP_UserDashboardSharing uds with (nolock) inner join tbl_STP_UserDashboards sd with (nolock) on sd.ownerId = uds.ownerId and sd.ownerType =uds.ownerType and sd.fspro_userId = uds.fspro_userId and sd.dashboard = uds.dashboard where ( ( uds.linkId = @fspro_userId and uds.linkType = 'USR' ) or ( uds.linkType = 'GRP' and exists ( select * from tbl_SEC_MemGrpLink mgl with (nolock) where mgl.groupId = uds.linkId and mgl.fspro_userId = @fspro_userId ) ) ) and sd.fspro_userId != @fspro_userId and sd.dashboard like @dashboardPattern and sd.ownerId = @ownerId and sd.ownerType = @ownerType ), userDashboards as ( select * from tbl_STP_UserDashboards with (nolock) where fspro_userId = @fspro_userId and dashboard like @dashboardPattern and ownerId = @ownerId and ownerType = @ownerType and sourceDashboard is not null ), merged as ( select ud.ownerId, ud.ownerType, ud.fspro_userId, ud.dashboard, ud.lastUpdated, sd.sourceDashboard, sd.sourceFspro_userId, sd.sourceLastUpdated, sd.sourceDashboardName from userDashboards ud with (nolock) full outer join sharedDashboards sd with (nolock) on sd.ownerId = ud.ownerId and sd.ownerType = ud.ownerType and sd.sourceDashboard = ud.sourceDashboard and sd.sourceFspro_userId = ud.sourceFspro_userId ) select * from merged select dashboard, sortRank, max( sortRank ) over () maxSortRank from tbl_STP_UserDashboards with (nolock) where ownerId = and ownertype = and dashboard like and fspro_userId = insert into tbl_STP_UserDashboards ( ownerId, ownerType, fspro_userId, dashboard, dashboardName, sortRank, sourceFspro_userId, sourceDashboard, lastUpdated ) values ( , , , , , , , , ) delete from tbl_STP_UserDashboards where ownerId = and ownerType = and dashboard = and fspro_userId = update tbl_STP_UserDashboards set dashboardName = , lastUpdated = where ownerId = and ownerType = and dashboard = and fspro_userId = select dashboard, dashboardName, sortRank, case when sourceDashboard is not null then 'Y' else 'N' end shared, shareConfig, case when exists ( select * from tbl_STP_userDashboardSharing uds where uds.ownerId = ud.ownerId and uds.ownerType = ud.ownerType and uds.fspro_userId = ud.fspro_userId and uds.dashboard = ud.dashboard ) then 'N' else 'Y' end private, case when dashboard like '%home' then 'Y' else 'N' end as home from tbl_STP_UserDashboards ud with (nolock) where ownerId = and ownerType = and fspro_userId = and dashboard like order by sortRank, dashboardName