Table valued function does not return data

Question

Testing my first Table Valued Function, I get no data, even while the SELECT works.
Not returning anything:

select * from GetMvtHistory('C1505 BLACK')

Returning correct data:

select row_number() OVER (order by DocId) as Id, detailId, Quant, 0 as Cumul
    FROM vwDetailsHist
    WHERE refer = 'C1505 BLACK'
    ORDER BY DocId;

TVF code:

ALTER FUNCTION [dbo].[GetMvtHistory]
(@Ref char(10))
RETURNS @MvtHist table
(
Id int,
[DetailId] int NULL, 
[Quant] int NULL, 
[Cumul] int NULL
)
WITH EXEC AS CALLER
AS
BEGIN
INSERT into @MvtHist
    select row_number() OVER (order by DocId) as Id, detailId, Quant, 0 as Cumul
        FROM vwDetailsHist
        WHERE refer = @Ref
        ORDER BY DocId;

RETURN

What am I missing ?? Thanks !

Also this doesn't need to be a multi statement TVF. Inline TVFs generally perform better. — Martin Smith
– Martin Smith, Commented Oct 30, 2013 at 11:18

usr · Accepted Answer · 2013-10-30 11:16:46Z

2

You've made the parameter a char(10). I cannot imagine that is the data type you need. You should probably use the same data type that the column has (which should probably be an nvarchar).

'C1505 BLACK' is 11 chars. Doesn't fit.

answered Oct 30, 2013 at 11:16

usr

172k38 gold badges251 silver badges380 bronze badges

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

2 Comments

Patrick Honorez Over a year ago

Oooops. I feel stupid now. You're perfectly right. Thanks. @Ref is indeed Varchar(15)

usr Over a year ago

Be sure to fix the string data types to be longer nvarchar types. That gets rid of issues like this and others.

Moumit · Accepted Answer · 2013-12-18 11:28:15Z

0

Set @Ref parameter to 50 or higher. Example:

@Ref varchar(500)

This will solve your problem.

edited Dec 18, 2013 at 11:28

answered Oct 30, 2013 at 11:26

Moumit

9,8709 gold badges59 silver badges66 bronze badges

1 Comment

Martin Smith Over a year ago

Though if ref is indexed and is actually varchar rather than nvarchar using this datatype can prevent an index seek.

Collectives™ on Stack Overflow

Table valued function does not return data

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related