Provance.ITAssetManagement.Report.ProvanceFunc_GetParentLocation.Install (Resource)

Element properties:

Source Code:

<Resource ID="Provance.ITAssetManagement.Report.ProvanceFunc_GetParentLocation.Install" Accessibility="Public" FileName="ProvanceFunc_GetParentLocation.sql" HasNullStream="false"/>

File Content: ProvanceFunc_GetParentLocation.sql

SET ANSI_NULLS ON


GO


SET QUOTED_IDENTIFIER ON


GO





-- Drop stored procedure if it already exists


IF EXISTS (


  SELECT * 


    FROM dbo.sysobjects 


   WHERE id = object_id(N'[dbo].[ProvanceFunc_GetParentLocation]')


     AND (OBJECTPROPERTY(id, N'IsScalarFunction') = 1 OR OBJECTPROPERTY(id, N'IsTableFunction') = 1) 


)


   DROP FUNCTION dbo.ProvanceFunc_GetParentLocation


GO





CREATE FUNCTION [dbo].[ProvanceFunc_GetParentLocation] (@locationID int)


RETURNS int


AS


BEGIN


	DECLARE 


		@parentLocation	int,


		@rowcount		int


	SELECT


		@parentLocation	= @locationID,


		@rowcount		= -1








	WHILE @parentLocation IS NOT NULL AND @rowcount <> 0


	BEGIN


		SELECT @parentLocation = A.LocationDimKey


		FROM LocationChildLocationFactvw A


		WHERE A.LocationContainsChildLocation_LocationDimKey = @parentLocation


		AND DeletedDate IS NULL


		


		SELECT @rowcount = @@RowCount


	END








	-- Return the result of the function


	RETURN @parentLocation





END


GO








GRANT EXECUTE ON dbo.ProvanceFunc_GetParentLocation TO reportuser


GO