CODEWITHSUNDEEP
sqlsql-server-2008sql-function
Afida A.
Afida A.

Reputation: 9

SQL Function: Result is empty

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.

Upvotes: 0

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

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)

Upvotes: 1

Related Questions