IF Else inside Inline Table Valued Functions

Question

Is it Possible to Use If Else Inside inline table valued function. i have a scalar function where i am using If Else Condition but, that query taking too much time to execute , what i want to convert it as a Inline table valued function. please suggest the way how can i do that.

ALTER FUNCTION [dbo].[TestFunctionFindSum]  
(  
 @ProductID bigint,  
 @TotalType nvarchar(200),   
 @OwnerUserID bigint,  
 @OrganizationID bigint,  
 @BusinessUnitID bigint,  
 @InventoryID bigint  
)  
RETURNS decimal(32,9)  
AS  
BEGIN  
 -- declare the return variable here  
 declare @OutputValue decimal(32,9)   
Declare @locationValue int =0 

 ---------------------------------------------------------------------  
 -- Getting Inventory Items Total as per the Total Type supplied  
 ---------------------------------------------------------------------  

 IF @TotalType = 'QuantityOnHand'     
 BEGIN  
  set @OutputValue = isnull((select sum(ii.[QuantityOnHand])   
           from dbo.InventoryItems ii, Inventory i               
           where   ii.ActiveStatus=1  
           and ii.ProductID = @ProductID  
            and ii.InventoryID = i.InventoryID  
        AND i.OwnerUserGroupID = case @OwnerUserID   
       when 0 then i.OwnerUserGroupID else @OwnerUserID end  
        AND i.OrganizationID = case @OrganizationID  
       when 0 then i.OrganizationID else @OrganizationID end  
        AND i.BusinessUnitID = case @BusinessUnitID  
       when 0 then i.BusinessUnitID else @BusinessUnitID end  
        AND i.InventoryID = case @InventoryID  
       when 0 then i.InventoryID else @InventoryID end), 0.00)  
 END  
 ELSE IF @TotalType = 'QuantityBooked'  
    BEGIN   
  set @OutputValue = isnull((select sum(ii.QuantitySold)   
           from dbo.InventoryItems ii, Inventory i               
           where   ii.ActiveStatus=1  
           and ii.ProductID = @ProductID  
            and ii.InventoryID = i.InventoryID  
        AND i.OwnerUserGroupID = case @OwnerUserID   
       when 0 then i.OwnerUserGroupID else @OwnerUserID end  
        AND i.OrganizationID = case @OrganizationID  
       when 0 then i.OrganizationID else @OrganizationID end  
        AND i.BusinessUnitID = case @BusinessUnitID  
       when 0 then i.BusinessUnitID else @BusinessUnitID end  
        AND i.InventoryID = case @InventoryID  
       when 0 then i.InventoryID else @InventoryID end), 0.00)  
 END   
 ELSE IF @TotalType = 'ProjectedQuantityOnHand'  
    BEGIN   
  set @OutputValue = isnull((select (sum(ii.QuantityOnHand) - sum(ii.QuantitySold))  
           from dbo.InventoryItems ii, Inventory i               
           where   ii.ActiveStatus=1  
           and ii.ProductID = @ProductID  
            and ii.InventoryID = i.InventoryID  
        AND i.OwnerUserGroupID = case @OwnerUserID   
       when 0 then i.OwnerUserGroupID else @OwnerUserID end  
        AND i.OrganizationID = case @OrganizationID  
       when 0 then i.OrganizationID else @OrganizationID end  
        AND i.BusinessUnitID = case @BusinessUnitID  
       when 0 then i.BusinessUnitID else @BusinessUnitID end  
        AND i.InventoryID = case @InventoryID  
       when 0 then i.InventoryID else @InventoryID end), 0.00)  
 END   
 return @OutputValue  

END

above is my scalar function.. any idea how to find the record based on inline table valued function.

What I was trying

CREATE FUNCTION [dbo].[TestFunctionFindSum](@ProductID bigint,  
 @TotalType nvarchar(200),   
 @OwnerUserID bigint,  
 @OrganizationID bigint,  
 @BusinessUnitID bigint,  
 @InventoryID bigint  )
RETURNS TABLE
AS RETURN
IF @TotalType = 'QuantityOnHand'     
 BEGIN  
   isnull((select sum(ii.[QuantityOnHand])   
           from dbo.InventoryItems ii, Inventory i               
           where   ii.ActiveStatus=1  
           and ii.ProductID = @ProductID  
            and ii.InventoryID = i.InventoryID  
        AND i.OwnerUserGroupID = case @OwnerUserID   
       when 0 then i.OwnerUserGroupID else @OwnerUserID end  
        AND i.OrganizationID = case @OrganizationID  
       when 0 then i.OrganizationID else @OrganizationID end  
        AND i.BusinessUnitID = case @BusinessUnitID  
       when 0 then i.BusinessUnitID else @BusinessUnitID end  
        AND i.InventoryID = case @InventoryID  
       when 0 then i.InventoryID else @InventoryID end), 0.00)  
 END  
GO

No, you can't use if because you can only have one statement. You need to have a case -statement in your select clause for picking the correct sum, maybe with a derived table or a CTE to make it more clear. — James Z, Sep 05 '16 at 14:02

score 1 · Accepted Answer · answered Sep 06 '16 at 06:54

You can use CASE statement as,

CREATE FUNCTION [dbo].[TestFunctionFindSum](@ProductID bigint,  
 @TotalType nvarchar(200),   
 @OwnerUserID bigint,  
 @OrganizationID bigint,  
 @BusinessUnitID bigint,  
 @InventoryID bigint  )
RETURNS TABLE
AS RETURN
SELECT 
    CASE WHEN @TotalType = 'QuantityOnHand' THEN 
                isnull((select sum(ii.[QuantityOnHand])   
                        from dbo.InventoryItems ii, Inventory i               
                        where   ii.ActiveStatus=1  
                        and ii.ProductID = @ProductID  
                        and ii.InventoryID = i.InventoryID  
                        AND i.OwnerUserGroupID = case @OwnerUserID   
                       when 0 then i.OwnerUserGroupID else @OwnerUserID end  
                        AND i.OrganizationID = case @OrganizationID  
                       when 0 then i.OrganizationID else @OrganizationID end  
                        AND i.BusinessUnitID = case @BusinessUnitID  
                       when 0 then i.BusinessUnitID else @BusinessUnitID end  
                        AND i.InventoryID = case @InventoryID  
                       when 0 then i.InventoryID else @InventoryID end), 0.00)  
        WHEN @TotalType = 'QuantityBooked'  THEN 
                isnull((select sum(ii.QuantitySold)   
                        from dbo.InventoryItems ii, Inventory i               
                        where   ii.ActiveStatus=1  
                        and ii.ProductID = @ProductID  
                        and ii.InventoryID = i.InventoryID  
                        AND i.OwnerUserGroupID = case @OwnerUserID   
                       when 0 then i.OwnerUserGroupID else @OwnerUserID end  
                        AND i.OrganizationID = case @OrganizationID  
                       when 0 then i.OrganizationID else @OrganizationID end  
                        AND i.BusinessUnitID = case @BusinessUnitID  
                       when 0 then i.BusinessUnitID else @BusinessUnitID end  
                        AND i.InventoryID = case @InventoryID  
                       when 0 then i.InventoryID else @InventoryID end), 0.00)  
        WHEN @TotalType = 'ProjectedQuantityOnHand'  THEN
                isnull((select (sum(ii.QuantityOnHand) - sum(ii.QuantitySold))  
                        from dbo.InventoryItems ii, Inventory i               
                        where   ii.ActiveStatus=1  
                        and ii.ProductID = @ProductID  
                        and ii.InventoryID = i.InventoryID  
                        AND i.OwnerUserGroupID = case @OwnerUserID   
                       when 0 then i.OwnerUserGroupID else @OwnerUserID end  
                        AND i.OrganizationID = case @OrganizationID  
                       when 0 then i.OrganizationID else @OrganizationID end  
                        AND i.BusinessUnitID = case @BusinessUnitID  
                       when 0 then i.BusinessUnitID else @BusinessUnitID end  
                        AND i.InventoryID = case @InventoryID  
                       when 0 then i.InventoryID else @InventoryID end), 0.00)  
        END AS OutputValue
 GO

IF Else inside Inline Table Valued Functions

1 Answers1