Summary Table

Categories Total Count
PII 0
URL 0
DNS 0
EKL 0
IP 0
PORT 0
VsID 0
CF 0
AI 0
VPD 0
PL 0
Other 0

File Content

?-- =============================================
-- Description: Returns list of Visns with a null handler that ties into the reporting.
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE seclyr.[uspRptGetUserVisnsListWithNullHandler]
@regionIds varchar(max),
@ccnRegionIds varchar(max),
@userId int,
@moduleIds varchar(max)
AS
DECLARE @resultsTable table
(
RowID int,
ModuleID tinyint,
RegionID tinyint,
CCNRegionID tinyint,
VisnID tinyint,
VisnCode varchar(10),
VisnDisplayName varchar(50),
VisnName varchar(100),
Inactive bit
)
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

BEGIN TRY

INSERT INTO @resultsTable (RowID, ModuleID, RegionID, CCNRegionID, VisnID, VisnCode,
VisnDisplayName, VisnName, Inactive)
EXEC seclyr.uspGetUserVisns @userId, @moduleIds;

--don't include RegionID and CCNRegionID in the returns, or there could be duplicates returned
SELECT DISTINCT
VisnID
, VisnCode
, VisnDisplayName
, VisnName
FROM (
SELECT
0 as VisnID
, '0' AS VisnCode
, '[Null Values]' AS VisnDisplayName
, '[Null Values]' AS VisnName
UNION
SELECT DISTINCT
VisnID
, VisnCode
, VisnDisplayName
, VisnName
FROM @resultsTable
WHERE
(RegionID In(SELECT [Value] FROM ufnSplit(',', @regionIds)) OR @regionIds IS NULL)
AND (CCNRegionID In(SELECT [Value] FROM ufnSplit(',', @ccnRegionIds)) OR @ccnRegionIds IS NULL)
--uncomment the next lines if need to do single select and have an "all" Visns
--UNION SELECT Null AS RowID, 0 AS RegionID, NULL AS VisnID, NULL AS VisnCode,
--'[All]' AS 'VisnDisplayName', '[All]' AS VisnName
) AS SubQ
ORDER BY VisnID, VisnDisplayName
;
END TRY
BEGIN CATCH
PRINT 'There was an while getting user VISN reporting lookups' + CHAR(13);
SELECT * FROM dbo.ufnGetErrorInfo();
EXEC uspRaiseErrorInfo;
END CATCH
END
GO