ServiceManager.Report.ConfigurationManagement.SP.GetListOfServices.Install (Resource)

Element properties:

Source Code:

<Resource ID="ServiceManager.Report.ConfigurationManagement.SP.GetListOfServices.Install" Accessibility="Public" FileName="ServiceManager.Report.ConfigurationManagement.SP.GetListOfServices.Install.sql"/>

File Content: ServiceManager.Report.ConfigurationManagement.SP.GetListOfServices.Install.sql

SET ANSI_NULLS ON


GO


SET QUOTED_IDENTIFIER ON


GO





-- Drop stored procedure if it already exists


IF EXISTS (


  SELECT * 


    FROM INFORMATION_SCHEMA.ROUTINES 


   WHERE SPECIFIC_SCHEMA = N'dbo'


     AND SPECIFIC_NAME = N'ServiceManager_Report_ConfigurationManagement_SP_GetListOfServices_Install' 


)


   DROP PROCEDURE dbo.ServiceManager_Report_ConfigurationManagement_SP_GetListOfServices_Install


GO





CREATE PROCEDURE dbo.ServiceManager_Report_ConfigurationManagement_SP_GetListOfServices_Install


	@DateFilter	nvarchar(256) = null,


	@StartDate	datetime,


	@EndDate	datetime,


	@Classification	nvarchar(max) = null,


	@Status		nvarchar(max) = null, 


	@Priority	nvarchar(max) = null,


	@ServiceDescription		nvarchar(max) = null,


	@IsBusinessService		bit = false,


	@OwnedByOrganization	nvarchar(max) = null


	


	


AS


BEGIN


  SET NOCOUNT ON





  DECLARE @Error int


  DECLARE @ExecError int


  


  DECLARE @tableStatus TABLE (value nvarchar(256))  


  INSERT @tableStatus (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Status)


  


  DECLARE @tablePriority TABLE (value nvarchar(256))  


  INSERT @tablePriority (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Priority)


  


  DECLARE @tableClassification TABLE (value nvarchar(256))  


  INSERT @tableClassification (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@Classification)





  DECLARE @tableServiceDescription TABLE (value nvarchar(256))  


  INSERT @tableServiceDescription(value)


  SELECT * FROM dbo.fn_CSVToTableInt(@ServiceDescription)


  


  DECLARE @tableOwnedByOrganization TABLE (value nvarchar(256))  


  INSERT @tableOwnedByOrganization (value)


  SELECT * FROM dbo.fn_CSVToTableInt(@OwnedByOrganization)


 


SELECT


          S.ServiceDimKey,


          S.ServiceDescription,


          S.BusinessDetailedDescription,


          S.IsBusinessService,


          S.OwnedByOrganization,


          S.AvailabilitySchedule,





          PriorityEnum.ServicePriorityValue,


          PriorityEnum.ServicePriorityId,


          StatusEnum.ServiceStatusValue,


          StatusEnum.ServiceStatusId,


          ClassificationEnum.ServiceClassificationValue,


          ClassificationEnum.ServiceClassificationId,








          E.LastModified





          FROM





          dbo.ServiceDimvw S





          INNER JOIN


          dbo.EntityDimvw E


          ON S.EntityDimKey = E.EntityDimKey





          left outer join


          dbo.ServicePriorityvw PriorityEnum


          ON PriorityEnum.ServicePriorityId = S.Priority_ServicePriorityId





          left outer join


          dbo.ServiceStatusvw StatusEnum


          ON StatusEnum.ServiceStatusId = S.Status_ServiceStatusId





          left outer join


          dbo.ServiceClassificationvw ClassificationEnum


          ON ClassificationEnum.ServiceClassificationId = S.Classification_ServiceClassificationId








          WHERE





         (((E.LastModified BETWEEN @StartDate AND @EndDate) ) AND  (@StartDate <= @EndDate)) AND





          ((-1 IN (@Status)) OR (S.Status IN (@Status))) AND


          ((-1 IN (@Priority)) OR (S.Priority IN (@Priority))) AND


          ((-1 IN (@Classification)) OR (S.Classification IN (@Classification))) AND


          ((@ServiceDescription IS NULL) OR (S.ServiceDescription LIKE @ServiceDescription)) AND


          ((@IsBusinessService IS NULL) OR (S.IsBusinessService = @IsBusinessService)) AND


          ((@OwnedByOrganization IS NULL) OR (S.OwnedByOrganization IN (@OwnedByOrganization)))








          ORDER BY ServiceDescription


          


          SET @Error = @@ERROR





QuitError:


  


  RETURN @Error


END


GO





GRANT EXECUTE ON dbo.ServiceManager_Report_ConfigurationManagement_SP_GetListOfServices_Install TO reportuser


GO