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 parent stations with null handler; ties into the reporting.
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE seclyr.[uspRptGetUserParentStationsListWithNullHandler]
@regionIds varchar(max),
@ccnRegionIds varchar(max),
@visnIds varchar(max),
@userId int,
@moduleIds varchar(max)
AS
DECLARE @resultsTable table
(
RowID int,
ModuleID tinyint,
RegionID tinyint, --this is VHA Region
CCNRegionID tinyint, --this is CCN Region
VisnID tinyint,
StationID smallint,
ParentStationID smallint,
Station3N varchar(10),
Station6A varchar(10),
StationDisplayName varchar(100),
StationName varchar(100),
IsParent bit,
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, StationID, ParentStationID, Station3N, Station6A
, StationDisplayName, StationName, IsParent, Inactive)
EXEC seclyr.uspGetUserParentStations @userId, @moduleIds;
--don't include RegionID and CCNRegionID in the returns, or there could be duplicates returned
SELECT DISTINCT
VisnID
, StationID
, ParentStationID
, Station3N
, Station6A
, StationDisplayName
, StationName
, IsParent
, Inactive
FROM (
SELECT
0 AS VisnID
, 0 as StationID
, 0 AS ParentStationID
, NULL As Station3N
, NULL As Station6A
, '[Null Values]' AS StationDisplayName
, '[Null Values]' AS StationName
, 1 AS IsParent
, 0 AS Inactive
UNION
SELECT DISTINCT
rx.VisnID AS VisnID
, rx.StationID AS StationID
, rx.ParentStationID AS ParentStationID
, rx.Station3N AS Station3N
, rx.Station6A AS Station6A
, rx.StationDisplayName AS StationDisplayName
, rx.StationName AS StationName
, rx.IsParent AS IsParent
, rx.Inactive AS Inactive
FROM @resultsTable rx
--INNER JOIN Stations vhaStations ON vhaStations.StationID = rx.StationID
--INNER JOIN Stations ccnStations ON ccnStations.StationID = rx.StationID
WHERE
(rx.VisnID In(SELECT [Value] FROM ufnSplit(',', @visnIds)) OR @visnIds IS NULL)
AND (rx.RegionID In(SELECT [Value] FROM ufnSplit(',', @regionIds)) OR @regionIds IS NULL)
AND (rx.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" parent stations
--UNION SELECT NULL AS RowID, 0 AS ModuleID, 0 AS VisnID
--, NULL AS StationID, NULL AS Station3N, NULL AS Station6A
--, '[All]' AS StationDisplayName, '[All]' AS StationName, 1 AS IsParent, 0 AS Inactive
) AS SubQ
ORDER BY VisnID, Station3N, StationDisplayName
;
END TRY
BEGIN CATCH
PRINT 'There was an while getting user parent station list with null handler' + CHAR(13);
SELECT * FROM dbo.ufnGetErrorInfo();
EXEC uspRaiseErrorInfo;
END CATCH
END
GO