Improving the complete SQL query in a stored procedure

Question

I have a saved proc that is used to populate a toolbar like grid in WPF. I clarified the issue over time, and frankly, performance could be better. I am now in the fact that I don’t know of good ways to improve the query, where I can get the performance taking into account the time it takes to complete.

Any help would be greatly appreciated. The following is stored proc:

    ALTER PROCEDURE [dbo].[spGetDashboardMainNew]
    AS
         BEGIN
             SET NOCOUNT ON;
             SELECT JC.job_number AS SalesOrder,
                    dbo.cust.Company AS Customer,
                    JC.Plan_ship_date AS PlannedShipDate,
                    JC.latest_ship_date AS LatestShipDate,
                    JC.planned_fab_complete AS PlanFabComplete,
                    JC.fldPromisedDate AS CommittedShipDate,
                    JC.Materials_of_Construction AS MatofCons,
                    JC.Fab_rating AS FabRating,
                    JC.ass_rating AS [Assembly Rating],
                    JC.shipped AS OpenShipped,
                    JC.electrical_status,
                    JC.dp_credit_status,
                    JC.fldCustRequestDate,
                    JC.commercial_terms,
                    JC.Approved_by,
                    fldLiquidatedDamages,
                    fldLiquidatedDamagesDesc,
                    CASE
                        WHEN hot_list = '0'
                             OR hot_list = 'No'
                        THEN 'No'
                        ELSE 'Yes'
                    END AS HotList,
                    CASE
                        WHEN JC.latest_ship_date IS NOT NULL
                             AND JC.fldPromisedDate IS NULL
                        THEN JC.latest_ship_date
                        ELSE JC.fldPromisedDate
                    END AS CommitDate,
                    CASE
                        WHEN dp_credit_status = 1
                             AND commercial_terms = 'Complete'
                        THEN 1
                        ELSE 0
                    END AS TermsMet,
                    CASE
                        WHEN JC.fldPromisedDate IS NULL
                        THEN 1
                        ELSE 0
                    END AS SortByDate,
                    dbo.tblShippingInfo.fldShipmentRequestID,
                    CASE
                        WHEN JC.fleInspectionType <> 'None'
                        THEN JC.fleInspectionType
                        ELSE NULL
                    END AS InspectionType,
                    JC.fldInspectionNotes,
                 JC.DueDateExtraTime,
                    CASE
                        WHEN JC.fleInspectionType <> 'None'
                             AND JC.fleInspectionType IS NOT NULL
                        THEN 1
                        ELSE 0
                    END AS InspectionDesc,
                    advanced_buying,
                    SortedOrder,
                    SalesOrderStatusGroup,
                    SalesOrderStatus,
                    SpecialOpList,
                    OperationDueBy,
                    test,
                    wpStatus,
                    SpecialOpsCount,
                    InChangeOrder,
                    RequiresSpecialPaint
             FROM dbo.cust
                  INNER JOIN dbo.Job_Control JC ON dbo.cust.CUST# = JC.Cust#
                  LEFT OUTER JOIN dbo.tblShippingInfo ON JC.job_number = dbo.tblShippingInfo.fldJobNumberID
                  OUTER APPLY
             (
                 SELECT dbo.udfGetManStatusFullSortOrder(JC.job_number) AS SortedOrder
             ) acolumn
                  OUTER APPLY
             (
                 SELECT ISNULL(dbo.udfGetManStatusBySOGroupNo(JC.job_number, 1), dbo.udfGetManStatusNew(JC.job_number)) AS SalesOrderStatusGroup
             ) bcolumn
                  OUTER APPLY
             (
                 SELECT ISNULL(dbo.udfGetManStatusBySOGroupNo(JC.job_number, 1), dbo.udfGetManStatusNew(JC.job_number)) AS SalesOrderStatus
             ) ccolumn
                  OUTER APPLY
             (
                 SELECT dbo.udfGetSpecialOperations(JC.job_number) AS SpecialOpList
             ) dcolumn
                  OUTER APPLY
             (
                 SELECT CASE
                            WHEN dbo.udfGetManStatusNew(JC.job_number) LIKE '%Approval%' OR dbo.udfGetManStatusNew(JC.job_number) LIKE '%Re-Approval%'
                            THEN JC.Approval_Due
                            ELSE dbo.udfGetDueByPerOperation(JC.job_number, 1)
                        END AS OperationDueBy
             ) ecolumn
                  OUTER APPLY
             (
                 SELECT dbo.udfGetGroupCount(JC.job_number) AS test
             ) fcolumn
                  OUTER APPLY
             (
                 SELECT dbo.udfGetOpenWorkPackagesByDesigner(JC.job_number) AS wpStatus
             ) gcolumn
                  OUTER APPLY
             (
                 SELECT dbo.udfGetSpecialOpsCount(JC.job_number) AS SpecialOpsCount
             ) hcolumn
                  OUTER APPLY
             (
                 SELECT dbo.udfIsActiveChangeOrder(JC.job_number) AS InChangeOrder
             ) icolumn
                  OUTER APPLY
             (
                 SELECT dbo.udfRequiresSpecialColor(JC.job_number) AS RequiresSpecialPaint
             ) jcolumn
             WHERE(JC.shipped = 'Open')
                  OR (JC.shipped = 'Hold');
         END;

The following are the functions that call:

        ALTER FUNCTION [dbo].[udfGetManStatusFullSortOrder] 
    (
        @SalesOrderNumber int
    )
    RETURNS INT
    AS
    BEGIN
        DECLARE @shipped nvarchar(50);
        DECLARE @eng_complete datetime;
        DECLARE @Drawing_Info nvarchar(50);
        DECLARE @Release_to_engineering datetime;
        DECLARE @Dwg_Sent datetime;
        DECLARE @Approval_done datetime;
        DECLARE @Detail bit;
        DECLARE @Release_to_shop datetime;
        DECLARE @OperationCount int;
        DECLARE @SortOrder INT;
        DECLARE @p as INT = 1;
        DECLARE @NonOpSortOrder INT;

        SET @NonOpSortOrder = dbo.udfIsOnHoldSort(@SalesOrderNumber)

        IF @NonOpSortOrder > 0 
            RETURN @nonopsortorder

            SELECT @SortOrder = (SELECT TOP(@p) dbo.ShopRouting.DashboardSortOrder
                FROM    dbo.Product INNER JOIN
                      dbo.ProductMfgGrouping ON dbo.Product.Record_no = dbo.ProductMfgGrouping.Record_no INNER JOIN
                      dbo.MfgGrouping ON dbo.ProductMfgGrouping.MfgGroupingID = dbo.MfgGrouping.MfgGroupingID INNER JOIN
                      dbo.GroupOperations ON dbo.MfgGrouping.MfgGroupingID = dbo.GroupOperations.MfgGroupingID INNER JOIN
                      dbo.ShopRouting ON dbo.GroupOperations.ShopRoutingID = dbo.ShopRouting.ShopRoutingID
                WHERE  (dbo.Product.Job_number = @SalesOrderNumber) AND (dbo.MfgGrouping.GroupingNumber = 1) AND (dbo.GroupOperations.Completed IS NULL)
                GROUP BY dbo.ShopRouting.Description, dbo.GroupOperations.NewSortOrder, dbo.ShopRouting.DashboardSortOrder
                ORDER BY dbo.GroupOperations.NewSortOrder);

        IF @SortOrder IS NOT NULL
                RETURN @SortOrder

        SELECT
            @shipped = shipped,
            @eng_complete = eng_complete,
            @Drawing_Info = Drawing_Info,
            @Release_to_engineering = Release_to_engineering,
            @Dwg_Sent = Dwg_Sent,
            @Approval_done = Approval_done,
            @Detail = Detail,
            @Release_to_shop = Release_to_shop

        FROM
            job_control

        WHERE
            job_number = @SalesOrderNumber

        BEGIN
            SET @OperationCount = flex2ksql.dbo.udfGetGroupOperationsCount(@SalesOrderNumber);
        END

        IF (@shipped = 'Hold')
            RETURN 33

        IF (@eng_complete IS NULL)
            BEGIN
                 IF (@Release_to_engineering IS NULL)

                    RETURN 32

                IF (@Drawing_Info = 'Approval' AND
                    @Release_to_engineering IS NOT NULL AND
                    @Dwg_Sent IS NOT NULL AND
                    @Approval_done IS NULL)

                    RETURN 28

                IF (@Drawing_Info = 'Re-Approval' AND
                    @Release_to_engineering IS NOT NULL AND
                    @Dwg_Sent IS NOT NULL AND
                    @Approval_done IS NULL)

                    RETURN 29

                IF (@Drawing_Info = 'Approval' AND
                    @Release_to_engineering IS NOT NULL AND
                    @Dwg_Sent IS NOT NULL AND
                    @Detail = 1 AND
                    @Approval_done IS NOT NULL)

                    If (@OperationCount) = 0 OR (@OperationCount IS NULL)
                        RETURN 27
                    ELSE
                        RETURN 26

                IF (@Drawing_Info = 'Approval' AND 
                    @Release_to_engineering IS NOT NULL)

                    RETURN 30

                IF (@Drawing_Info = 'Re-Approval' AND
                    @Release_to_engineering IS NOT NULL AND
                    @Dwg_Sent IS NULL AND
                    @Approval_done IS NULL)

                    RETURN 31

                IF (@Drawing_Info = 'Certified' AND
                    @Release_to_engineering IS NOT NULL AND
                    @Detail = 1)

                    If (@OperationCount) = 0 OR (@OperationCount IS NULL)
                        RETURN 27
                    ELSE
                        RETURN 26

                IF (@Drawing_Info = 'No Drawing Required' AND
                    @Release_to_engineering IS NOT NULL)

                    If (@OperationCount) = 0 OR (@OperationCount IS NULL)
                        RETURN 27
                    ELSE
                        RETURN 26

                IF (@Drawing_Info = 'Certified')

                    If (@OperationCount) = 0 OR (@OperationCount IS NULL)
                        RETURN 27
                    ELSE
                        RETURN 26

                IF (@Drawing_Info = 'Sales Drawing' AND
                    @Release_to_engineering IS NOT NULL AND
                    @Release_to_shop IS NOT NULL)

                    If (@OperationCount) = 0 OR (@OperationCount IS NULL)
                        RETURN 27
                    ELSE
                        RETURN 26
                ELSE
                    RETURN 27

            END
        ELSE
            BEGIN
                RETURN 27
            END
        RETURN 99



        ALTER FUNCTION [dbo].[udfGetManStatusBySOGroupNo] 
    (
        @SalesOrder int, @GroupNo int
    )
    RETURNS varchar(100)
    AS
    BEGIN

        DECLARE @ManStatus varchar(100);
        DECLARE @p as INT = 1;
        DECLARE @NonOpSortOrder INT;

        SET @NonOpSortOrder = dbo.udfIsOnHoldSort(@SalesOrder)

        IF @NonOpSortOrder > 0 
            RETURN 'Hold'
        ELSE

                SELECT @ManStatus = (SELECT TOP(@p) dbo.ShopRouting.Description
                FROM dbo.Product INNER JOIN
                      dbo.ProductMfgGrouping ON dbo.Product.Record_no = dbo.ProductMfgGrouping.Record_no INNER JOIN
                      dbo.MfgGrouping ON dbo.ProductMfgGrouping.MfgGroupingID = dbo.MfgGrouping.MfgGroupingID INNER JOIN
                      dbo.GroupOperations ON dbo.MfgGrouping.MfgGroupingID = dbo.GroupOperations.MfgGroupingID INNER JOIN
                      dbo.ShopRouting ON dbo.GroupOperations.ShopRoutingID = dbo.ShopRouting.ShopRoutingID
                WHERE  (dbo.Product.Job_number = @SalesOrder) AND (dbo.MfgGrouping.GroupingNumber = @GroupNo) AND (dbo.GroupOperations.Completed is null)
                GROUP BY dbo.ShopRouting.Description, dbo.Product.Model_NO, dbo.GroupOperations.NewSortOrder
                ORDER BY dbo.GroupOperations.NewSortOrder);

                RETURN @ManStatus


        ALTER FUNCTION [dbo].[udfGetManStatusNew] 
    (
        @SalesOrderNumber int
    )
    RETURNS nvarchar(50)
    AS
    BEGIN
        DECLARE @shipped nvarchar(50);
        DECLARE @eng_complete datetime;
        DECLARE @Drawing_Info nvarchar(50);
        DECLARE @Release_to_engineering datetime;
        DECLARE @Dwg_Sent datetime;
        DECLARE @Approval_done datetime;
        DECLARE @Detail bit;
        DECLARE @Release_to_shop datetime;
        DECLARE @OperationCount int;

        SELECT
            @shipped = shipped,
            @eng_complete = eng_complete,
            @Drawing_Info = Drawing_Info,
            @Release_to_engineering = Release_to_engineering,
            @Dwg_Sent = Dwg_Sent,
            @Approval_done = Approval_done,
            @Detail = Detail,
            @Release_to_shop = Release_to_shop

        FROM
            job_control

        WHERE
            job_number = @SalesOrderNumber

        BEGIN
            SET @OperationCount = flex2ksql.dbo.udfGetGroupOperationsCount(@SalesOrderNumber);
        END

        IF (@shipped = 'Hold')
            RETURN 'Hold'

        IF (@eng_complete IS NULL)
            BEGIN
              IF (@Release_to_engineering IS NULL)

                    RETURN 'Not Released'

                IF (@Drawing_Info = 'Approval' AND
                    @Release_to_engineering IS NOT NULL AND
                    @Dwg_Sent IS NOT NULL AND
                    @Approval_done IS NULL)

                    RETURN 'Approval Out'

                IF (@Drawing_Info = 'Re-Approval' AND
                    @Release_to_engineering IS NOT NULL AND
                    @Dwg_Sent IS NOT NULL AND
                    @Approval_done IS NULL)

                    RETURN 'Re-Approval Out'

                IF (@Drawing_Info = 'Approval' AND
                    @Release_to_engineering IS NOT NULL AND
                    @Dwg_Sent IS NOT NULL AND
                    @Detail = 1 AND
                    @Approval_done IS NOT NULL)

                    If (@OperationCount) = 0 OR (@OperationCount IS NULL)
                        RETURN 'Routing'
                    ELSE
                        RETURN 'Detail'

                IF (@Drawing_Info = 'Approval' AND 
                    @Release_to_engineering IS NOT NULL)

                    RETURN 'Approval To Be Done'

                IF (@Drawing_Info = 'Re-Approval' AND
                    @Release_to_engineering IS NOT NULL AND
                    @Dwg_Sent IS NULL AND
                    @Approval_done IS NULL)

                    RETURN 'Re-Approval To Be Done'
                IF (@Drawing_Info = 'Certified' AND
                    @Release_to_engineering IS NOT NULL AND
                    @Detail = 1)

                    If (@OperationCount) = 0 OR (@OperationCount IS NULL)
                        RETURN 'Routing'
                    ELSE
                        RETURN 'Detail'

                IF (@Drawing_Info = 'No Drawing Required' AND
                    @Release_to_engineering IS NOT NULL)

                    If (@OperationCount) = 0 OR (@OperationCount IS NULL)
                        RETURN 'Routing'
                    ELSE
                        RETURN 'Detail'

                IF (@Drawing_Info = 'Certified')

                    If (@OperationCount) = 0 OR (@OperationCount IS NULL)
                        RETURN 'Routing'
                    ELSE
                        RETURN 'Detail'

                IF (@Drawing_Info = 'Sales Drawing' AND
                    @Release_to_engineering IS NOT NULL AND
                    @Release_to_shop IS NOT NULL)

                    If (@OperationCount) = 0 OR (@OperationCount IS NULL)
                        RETURN 'Routing'
                    ELSE
                        RETURN 'Detail'
                ELSE
                    RETURN 'Routing'

            END
        ELSE
            BEGIN
                RETURN 'Routing'
            END
        RETURN NULL


ALTER FUNCTION [dbo].[udfGetSpecialOperations] 
(
@SalesOrder int
)
RETURNS nvarchar(MAX)
AS
BEGIN   
DECLARE @returnVal nvarchar(MAX)

SELECT @returnVal = Stuff((SELECT N', ' + dbo.ShopRouting.Description FROM dbo.Product INNER JOIN
        dbo.ProductMfgGrouping ON dbo.Product.Record_no = dbo.ProductMfgGrouping.Record_no INNER JOIN
        dbo.MfgGrouping ON dbo.ProductMfgGrouping.MfgGroupingID = dbo.MfgGrouping.MfgGroupingID INNER JOIN
        dbo.GroupOperations ON dbo.MfgGrouping.MfgGroupingID = dbo.GroupOperations.MfgGroupingID INNER JOIN
        dbo.ShopRouting ON dbo.GroupOperations.ShopRoutingID = dbo.ShopRouting.ShopRoutingID                         
    WHERE (dbo.ShopRouting.Standard = 0) AND (dbo.Product.Job_number = @SalesOrder)
    GROUP BY dbo.ShopRouting.Description, dbo.ShopRouting.DashboardSortOrder
    ORDER BY dbo.ShopRouting.DashboardSortOrder DESC 
    FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'')

RETURN @returnVal


        ALTER FUNCTION [dbo].[udfGetDueByPerOperation] 
    (
        @SalesOrder int, @GroupNo int
    )
    RETURNS varchar(100)
    AS
    BEGIN
        DECLARE @ManStatus varchar(100);
        DECLARE @p as INT = 1;

        SELECT @ManStatus = (SELECT TOP(@p) dbo.GroupOperations.StartBy
    FROM     dbo.Product INNER JOIN
                      dbo.ProductMfgGrouping ON dbo.Product.Record_no = dbo.ProductMfgGrouping.Record_no INNER JOIN
                      dbo.MfgGrouping ON dbo.ProductMfgGrouping.MfgGroupingID = dbo.MfgGrouping.MfgGroupingID INNER JOIN
                      dbo.GroupOperations ON dbo.MfgGrouping.MfgGroupingID = dbo.GroupOperations.MfgGroupingID INNER JOIN
                      dbo.ShopRouting ON dbo.GroupOperations.ShopRoutingID = dbo.ShopRouting.ShopRoutingID
    WHERE  (dbo.Product.Job_number = @SalesOrder) AND (dbo.MfgGrouping.GroupingNumber = @GroupNo) AND (dbo.GroupOperations.Completed IS NULL) AND (dbo.GroupOperations.Active = 0)
    GROUP BY dbo.ShopRouting.[Description], dbo.Product.Model_NO, dbo.GroupOperations.NewSortOrder, dbo.GroupOperations.StartBy
    ORDER BY dbo.GroupOperations.NewSortOrder);

        RETURN @ManStatus


        ALTER FUNCTION [dbo].[udfGetGroupCount] 
    (
        @SalesOrder int
    )
    RETURNS varchar(MAX)
    AS
    BEGIN
        DECLARE @result varchar(MAX)
        DECLARE @result2 varchar(MAX)

    SET @result = ''

    SELECT @result = @result + Description + ': ' + Cast(StatusCount as varchar(5)) + ', ' FROM vwLineItemGroupStatusCounts WHERE Job_number = @SalesOrder
    ORDER BY vwLineItemGroupStatusCounts.NewSortOrder

    SELECT @result2 = substring(@result, 0, len(@result)) --trim extra "," at end

        -- Return the result of the function
        RETURN @result2


        ALTER FUNCTION [dbo].[udfGetOpenWorkPackagesByDesigner] 
    (
        @SalesOrder int
    )
    RETURNS varchar(MAX)
    AS
    BEGIN
        DECLARE @result varchar(MAX)
        DECLARE @result2 varchar(MAX)

    SET @result = ''

    --SELECT @result = @result + Fldusername + ': ' + Cast(StatusCount as varchar(5)) + ', ' FROM vwOpenWorkPackagesByDesigner WHERE Job_number = @SalesOrder
    SELECT @result = @result + eng_difficulty_category + ' ' FROM vwOpenWorkPackagesEngRating WHERE job_number = @SalesOrder
    SELECT @result = @result + Fldusername + ' ' + Cast(coalesce(fldPriority,'none') as varchar(5)) + ' ' + cast(PercentComplete as varchar(10)) + '% ' + fldTaskCategoryAbbr + '; ' FROM vwOpenWorkPackagesByDesigner WHERE fldSalesOrder = @SalesOrder

    SELECT @result2 = substring(@result, 0, len(@result)) --trim extra "," at end

        -- Return the result of the function
        RETURN @result2


        ALTER FUNCTION [dbo].[udfGetSpecialOpsCount] 
    (
        @SalesOrder int
    )
    RETURNS int
    AS
    BEGIN

        DECLARE @returnVal int;

        SELECT @returnVal = COUNT(dbo.GroupOperations.GroupOperationID)
    FROM     dbo.Product INNER JOIN
                      dbo.ProductMfgGrouping ON dbo.Product.Record_no = dbo.ProductMfgGrouping.Record_no INNER JOIN
                      dbo.MfgGrouping ON dbo.ProductMfgGrouping.MfgGroupingID = dbo.MfgGrouping.MfgGroupingID INNER JOIN
                      dbo.GroupOperations ON dbo.MfgGrouping.MfgGroupingID = dbo.GroupOperations.MfgGroupingID INNER JOIN
                      dbo.ShopRouting ON dbo.GroupOperations.ShopRoutingID = dbo.ShopRouting.ShopRoutingID
    WHERE  (dbo.ShopRouting.Standard = 0) AND (dbo.Product.Job_number = @SalesOrder) AND (dbo.ShopRouting.ShopRoutingID NOT IN (15,16))

        If (@returnVal) = 0 OR (@returnVal IS NULL)
            SET @returnVal = 0;
        Return @returnVal;


        ALTER FUNCTION [dbo].[udfIsActiveChangeOrder] (@SalesOrder numeric(18, 0))

    RETURNS Bit

    AS

    BEGIN

        DECLARE @isCO Bit
        DECLARE @SOFound as numeric(18)

        SET @SOFound = 0

            BEGIN
            SELECT
                @SOFound = fldSalesOrder        
            FROM
                [flex2kSQL].[dbo].[vwChangeOrdersAllActive]
            WHERE
                fldSalesOrder = @SalesOrder
            END


            if @SOFound =0
                SET @isCO=0
            else
                SET @isCO=1


        RETURN @isCO


        ALTER FUNCTION [dbo].[udfRequiresSpecialColor] 
    (
        @SalesOrder INT
    )
    RETURNS INT
    AS
    BEGIN
        DECLARE @ReturnVal int;

        IF EXISTS (SELECT 1
            FROM dbo.MfgGrouping INNER JOIN
                dbo.ProductMfgGrouping ON dbo.MfgGrouping.MfgGroupingID = dbo.ProductMfgGrouping.MfgGroupingID INNER JOIN
                dbo.Product ON dbo.ProductMfgGrouping.Record_no = dbo.Product.Record_no INNER JOIN
                dbo.GroupOperations ON dbo.MfgGrouping.MfgGroupingID = dbo.GroupOperations.MfgGroupingID
            WHERE (dbo.Product.Job_number = @SalesOrder) AND (dbo.GroupOperations.ShopRoutingID IN (23, 24, 25)))
        BEGIN
            SET @ReturnVal = 1
        END
        ELSE
        BEGIN
            SET @ReturnVal = 0
        END

        Return @ReturnVal;

Below is a line of sample data from an executable file (each line is deleted, but the testing order):

SalesOrder PlannedShipDate LatestShipDate PlanFabComplete CommittedShipDate MatofCons FabRating OpenShipped electrical_status dp_credit_status fldCustRequestDate commercial_terms Approved_by fldLiquidatedDamages fldLiquidatedDamagesDesc CommitDate TermsMet SortByDate fldShipmentRequestID InspectionType fldInspectionNotes DueDateExtraTime InspectionDesc advanced_buying SortedOrder SalesOrderStatusGroup SalesOrderStatus SpecialOpList OperationDueBy wpStatus SpecialOpsCount InChangeOrder RequiresSpecialPaint 76506 Test Customer 6/16/2020 12/31/2020 NULL 11/28/2016 SS CC Open In Test 0 9/29/2016 NULL Premount/Prewire 0 NULL 11/28/2016 0 0 NULL NULL NULL 7 0 26 NULL 6/2/2020 : 6 NULL 0 0 0

, , , .

- EDIT - , .

Answer 1

- , .

, , , , . , ( , , ).

, , udfRequiresSpecialColor :

SELECT
    dbo.Product.Job_number

FROM 
    dbo.MfgGrouping 

INNER JOIN
    dbo.ProductMfgGrouping 
    ON dbo.MfgGrouping.MfgGroupingID = dbo.ProductMfgGrouping.MfgGroupingID 

INNER JOIN
    dbo.Product 
    ON dbo.ProductMfgGrouping.Record_no = dbo.Product.Record_no 

INNER JOIN 
    dbo.GroupOperations 
    ON dbo.MfgGrouping.MfgGroupingID = dbo.GroupOperations.MfgGroupingID
    AND (dbo.GroupOperations.ShopRoutingID IN (23, 24, 25))

, Job_number, , .

OUTER APPLY:

SELECT
    ...
    RequiresSpecialPaint
    ...
OUTER APPLY
(
    SELECT dbo.udfRequiresSpecialColor(JC.job_number) AS RequiresSpecialPaint
) AS jcolumn

... LEFT JOIN :

SELECT
    ...
    IIF(jobs_requiring_special_paint.job_number IS NOT NULL, 1, 0) AS RequiresSpecialPaint
    ...

LEFT JOIN
    (
        [here you can either a call to the new udfRequiresSpecialColor defined as a database view or an inlinable function, or simply include the code above as a subquery]
    ) AS jobs_requiring_special_paint
    ON (JC.job_number = jobs_requiring_special_paint.job_number)

, , NULL , , - 0/1 select, .

, , , , .

, , .

, , OUTER APPLY, ( ) , ON.

, udfGetDueByPerOperation, , , udfRequiresSpecialColor ( MfgGrouping, ProductMfgGrouping, Product, GroupOperations), (INNER JOINs ), ShopRouting .

, ( , , / WITH), , JOIN . , , , , , , , , . , , .

, , udfGetManStatusNew, , , .

, , , , , , ​​ _, job_control , OUTER APPLY, udfGetGroupOperationsCount ( , ) @OperationCount ( ). , "" , , ).

, , , , , .

, , - , .

, . , .

EDIT: , .

, , , , . , , ( , ).

/ ( , , DUAL SQL Server, Oracle, ):

CREATE FUNCTION [dbo].[fxn_imperative] 
(   
     @dummy     VARCHAR(1)
)
RETURNS INT
AS
BEGIN
    DECLARE @ReturnVal int;

    IF EXISTS (SELECT 1 FROM DUAL WHERE DUMMY = @dummy)
        BEGIN
            SET @ReturnVal = 1
        END
    ELSE
        BEGIN
            SET @ReturnVal = 0
        END

    Return @ReturnVal;
END
-- called like so: SELECT dbo.fxn_imperative('X') AS dummy_exists;


CREATE FUNCTION [dbo].[fxn_inline] 
(   
     @dummy     VARCHAR(1)
)
RETURNS TABLE 
AS
RETURN 
(
    SELECT ISNULL( (SELECT 1 FROM DUAL WHERE DUMMY = @dummy), 0) AS dummy_exists
)
-- called like so: SELECT * FROM fxn_inline('X');

, , .

LEFT JOIN - ( ) ( _ , , ). , .

, ( , OUTER APPLYed), ( , - / ):

CREATE FUNCTION [dbo].[udfRequiresSpecialColor2] 
(   
     @SalesOrder INT
)
RETURNS TABLE 
AS
RETURN 
(

    WITH special_paint_subquery AS
    (
        SELECT
            dbo.Product.Job_number

        FROM 
            dbo.MfgGrouping 

        INNER JOIN
            dbo.ProductMfgGrouping 
            ON dbo.MfgGrouping.MfgGroupingID = dbo.ProductMfgGrouping.MfgGroupingID 

        INNER JOIN
            dbo.Product 
            ON dbo.ProductMfgGrouping.Record_no = dbo.Product.Record_no 

        INNER JOIN 
            dbo.GroupOperations 
            ON dbo.MfgGrouping.MfgGroupingID = dbo.GroupOperations.MfgGroupingID
            AND (dbo.GroupOperations.ShopRoutingID IN (23, 24, 25))

    )

    SELECT ISNULL( (SELECT 1 FROM special_paint_subquery WHERE dbo.Product.Job_number = @SalesOrder), 0) AS requires_special_paint
)

, , .

, udfRequiresSpecialColor - , job_number, .

, ( WITH special_paint_subquery ... , WHERE dbo.Product.Job_number = @SalesOrder , SELECT , NULL- , ).