Gets the Machine Company by ResourceID using Machine FQDN or OU.

ufn_GetCompany_by_ResourceID.sql

/*
*********************************************************************************************************
* Created by Ioan Popovici, 2015-08-18       | Requirements: CM_Tools Database                          *
* ======================================================================================================*
* Modified by                   |    Date    | Revision | Comments                                      *
*_______________________________________________________________________________________________________*
* Ioan Popovici                 | 2015-08-18 | v1.0     | First version                                 *
*-------------------------------------------------------------------------------------------------------*
* Credit to: Michelle Ufford http://sqlfool.com.                                                        *
*********************************************************************************************************

    .SYNOPSIS
        This SQL Function is used to get the Machine Company by ResourceID.
    .DESCRIPTION
        This SQL Function is used to get the Machine Company by ResourceID using Machine FQDN or OU.
*/

/*##=============================================*/
/*## QUERY BODY
/*##=============================================*/
/* #region QueryBody */

USE [CM_Tools]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

IF EXISTS (
    SELECT  [OBJECT_ID]
    FROM    SYS.OBJECTS
    WHERE   NAME = 'ufn_GetCompany_by_ResourceID'
    )
    DROP FUNCTION dbo.ufn_GetCompany_by_ResourceID;
GO

CREATE FUNCTION [dbo].[ufn_GetCompany_by_ResourceID](@pResourceID INT)
RETURNS VARCHAR(200)
AS
    BEGIN
        DECLARE @RET VARCHAR(200);
        SELECT @RET =
        (
            SELECT TOP 1
                CASE
                    WHEN ([ou].[System_OU_Name0] LIKE '%XXX%' OR [rn].[Resource_Names0] LIKE '%XXX%') THEN 'XXX'
                    WHEN ([ou].[System_OU_Name0] LIKE '%YYY%' OR [rn].[Resource_Names0] LIKE '%YYY%') THEN 'YYY'
                    ELSE '0-UNKNOWN'
                END
        FROM [CM_A01].[dbo].[v_RA_System_ResourceNames] [rn]
        LEFT JOIN [CM_A01].[dbo].[v_RA_System_SystemOUName] AS [ou] ON @pResourceID = [ou].[ResourceID]
        WHERE [rn].[Resource_Names0] IS NOT NULL
            AND [rn].[ResourceID] = @pResourceID
        )
        IF @RET IS NULL SET @RET = '0-UNKNOWN'
        RETURN @RET;
    END;

/* #endregion */
/*##=============================================*/
/*## END QUERY BODY                              */
/*##=============================================*/