• Thom A - Thursday, October 12, 2017 8:43 AM

    You're attempting to set the value of the variable @ret outside the CASE statement, and inside. Remove the assignment of the variable inside the sub-queries and it'll work fine.

    (Also, yes, see Luis' link).

    New "C" (which works).  Awesome.  Many thanks.

    USE [W_SE-MFG]
    GO
    /****** Object: UserDefinedFunction [dbo].[QtyOnHandNoExcl]  Script Date: 2017-10-12 09:57:22 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER FUNCTION [dbo].[QtyOnHandNoExcl]
    (@SC nVarChar(30), @Wh nVarchar(3), @CIE varchar(1))
    RETURNS Decimal(8,3) 
    AS 
    BEGIN
    DECLARE @ret Decimal(8,3)
    SELECT @ret = CASE @CIE
    WHEN 'K' THEN
      (SELECT SUM(QtyOnHand1) 
      FROM SysproSStar.dbo.InvMultBin p
      WHERE p.StockCode = @SC
       AND p.Warehouse = @Wh
            AND NOT p.Note Like 'EXCL%')
    WHEN 'N' THEN
      (SELECT SUM(QtyOnHand1) 
      FROM SysproSStarNZ.dbo.InvMultBin p
      WHERE p.StockCode = @SC
       AND p.Warehouse = @Wh
            AND NOT p.Note Like 'EXCL%')
    WHEN 'O' THEN
      (SELECT SUM(QtyOnHand1) 
      FROM SysproSStarAust.dbo.InvMultBin p
      WHERE p.StockCode = @SC
       AND p.Warehouse = @Wh
            AND NOT p.Note Like 'EXCL%')
    END;
    RETURN(@ret);
    END;