If you decide you need an inline table-valued function for performance reasons, try this: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 TABLE WITH SCHEMABINDING
RETURN
SELECT CONVERT(decimal(8,3), SUM(X.QtyOnHand1)) AS RET
FROM (
SELECT 'K' AS CIE, QtyOnHand1
FROM SysproSStar.dbo.InvMultBin AS p
WHERE @CIE = 'K'
AND p.StockCode = @SC
AND p.Warehouse = @Wh
AND NOT p.Note Like 'EXCL%'
UNION ALL
SELECT 'N', QtyOnHand1
FROM SysproSStarNZ.dbo.InvMultBin AS p
WHERE @CIE = 'N'
AND p.StockCode = @SC
AND p.Warehouse = @Wh
AND NOT p.Note Like 'EXCL%'
UNION ALL
SELECT 'O', QtyOnHand1
FROM SysproSStarAust.dbo.InvMultBin AS p
WHERE @CIE = 'K'
AND p.StockCode = @SC
AND p.Warehouse = @Wh
AND NOT p.Note Like 'EXCL%'
) AS X;
GO
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)