SQL Function: Result is empty

Question

I created a function using these: -

CREATE Function dbo.FuncDailyInOut_AA_20190401 
    (@Date1 AS DATE,
     @Date2 AS DATE,
     @Outlet AS CHAR)
RETURNS TABLE
AS
    RETURN  
        (SELECT 
             afldat AS DATE, kstdrcode AS OUTLET, artcode AS ITEMCODE, 
             oms45 AS DESCRIPTION, aant_gelev AS QTY, unitcode AS UOM 
         FROM 
             orsrg 
         WHERE 
             artcode NOT IN ('10', 'NULL', 'O', 'S', '999-9008')
             AND afldat BETWEEN @Date1 AND @Date2 -- Parameter 1 & 2
             AND kstdrcode = @Outlet    --= 'CTP' --Parameter 3
        )

I have the select statement which when executed it return 1243 rows, however when I use the function, is it not showing any result: -

SELECT * 
FROM dbo.FuncDailyInOut_AA_20190401 ('2019-02-02', '2019-02-28', 'CTP')

Am I missing something? Thank you in advance.

Gordon Linoff · Accepted Answer · 2019-10-21 01:45:59Z

1

Never use character types in SQL Server without a length. The default varies by context and is often not what you expect.

This:

@Outlet as char

is really:

@Outlet as char(1)

You are passing in 'CTP', but the function is only getting 'C'.

Instead, use something like:

@Outlet as varchar(255)

answered Oct 21, 2019 at 1:45

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Function: Result is empty

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related