-- 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
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