Help with a SQL Server subquery or stored procedure

SELECT base, LOT, SPLIT, SUB, SEQ
, CASE WHEN WareHouse = 'TBD' THEN (SELECT TOP 1 t2.WareHouse FROM CT_Work_Order_Warehouse_Headers_Pass_02 t2 WHERE t1.Base = t2.Base and t1.Lot = t2.Lot and t1.Split = t2.Split and t1.sub = t2.sub and t1.Seq > t2.Seq and t2.WareHouse <> 'TBD' ORDER BY t2.SEQ desc) ELSE Warehouse END
FROM CT_Work_Order_Warehouse_Headers_Pass_02 t1

Select allOpen in new window

SELECT     WORKORDER_BASE_ID, WORKORDER_LOT_ID, WORKORDER_SPLIT_ID, WORKORDER_SUB_ID, SEQUENCE_NO, 
                      CASE WHEN Op_WareHouse = 'TBD' THEN
                          (SELECT     TOP 1 t2.Op_WareHouse
                            FROM          CT_WorkOrder_Operations_WareHouse_Pass_01 AS t2 WITH (NOLOCK)
                            WHERE      t1.WORKORDER_BASE_ID = t2.WORKORDER_BASE_ID AND t1.WORKORDER_LOT_ID = t2.WORKORDER_LOT_ID AND 
                                                   t1.WORKORDER_SPLIT_ID = t2.WORKORDER_SPLIT_ID AND t1.WORKORDER_SUB_ID = t2.WORKORDER_SUB_ID AND 
                                                   t1.SEQUENCE_NO > t2.SEQUENCE_NO AND t2.Op_WareHouse <> 'TBD'
                            ORDER BY t2.SEQUENCE_NO DESC) ELSE Op_WareHouse END AS Expr1
FROM         dbo.CT_WorkOrder_Operations_Warehouse_Pass_01 AS t1 WITH (NOLOCK)

Select allOpen in new window

ALTER FUNCTION [dbo].[fn_CT_OP_Warehouse]
    (
        @TYPE NCHAR(1) ,
        @BASE NVARCHAR(30) ,
        @LOT NVARCHAR(3) ,
        @SPLIT NVARCHAR(3) ,
        @SUB NVARCHAR(3) ,
        @SEQ smallint,
        @WAREHOUSE nvarchar(30)
    )
RETURNS NVARCHAR(30)

AS
    BEGIN
        DECLARE @MSEQ smallint;
        SELECT @MSEQ = MAX(CT_WorkOrder_Operations_Warehouse_Pass_01.SEQUENCE_NO) FROM CT_WorkOrder_Operations_Warehouse_Pass_01 WHERE WORKORDER_TYPE = @TYPE AND WORKORDER_BASE_ID = @BASE AND WORKORDER_LOT_ID = @LOT AND WORKORDER_SPLIT_ID = @SPLIT AND WORKORDER_SUB_ID = @SUB AND SEQUENCE_NO <= @SEQ AND OP_Warehouse not like N'TBD'
        DECLARE @OPWAREHOUSE NVARCHAR(30);
        SELECT @OPWAREHOUSE = OP_Warehouse FROM CT_WorkOrder_Operations_Warehouse_Pass_01 WHERE WORKORDER_TYPE = @TYPE AND WORKORDER_BASE_ID = @BASE AND WORKORDER_LOT_ID = @LOT AND WORKORDER_SPLIT_ID = @SPLIT AND WORKORDER_SUB_ID = @SUB AND SEQUENCE_NO = @MSEQ
        RETURN @OPWAREHOUSE
    END

Select allOpen in new window

ALTER FUNCTION [dbo].[fn_CT_OPERATION_Warehouse]
    (
        @TYPE NCHAR(1) ,
        @BASE NVARCHAR(30) ,
        @LOT NVARCHAR(3) ,
        @SPLIT NVARCHAR(3) ,
        @SUB NVARCHAR(3) ,
        @SEQ smallint
    )
RETURNS NVARCHAR(30)

AS
    BEGIN
        DECLARE @OPWAREHOUSE NVARCHAR(30);
        SELECT @OPWAREHOUSE = TOP 1 Op_WareHouse FROM  CT_WorkOrder_Operations_WareHouse_Pass_01 WITH (NOLOCK) WHERE WORKORDER_BASE_ID = @BASE AND WORKORDER_LOT_ID = @LOT AND WORKORDER_SPLIT_ID = @SPLIT AND WORKORDER_SUB_ID = @SUB AND SEQUENCE_NO <= @SEQ AND Op_WareHouse <> 'TBD' ORDER BY SEQUENCE_NO DESC
        RETURN @OPWAREHOUSE
    END

Select allOpen in new window