Invalid object name error for a function used in sql server

Question

I am using sql-server 2012:

drop table ttt
create table ttt(id int,amt int)
insert into ttt values(1,100),(2,200),(1,200),(3,400),(1,500)

create function dbo.t1(@id int)
returns int
with returns null on null input
as
begin
declare @amt_total int
select @amt_total=sum(amt) from ttt
where id=@id
return @amt_total
end

select t.id,t.amt,f.id from ttt t cross apply dbo.t1(t.id) f

The code returns following error on execution: Msg 208, Level 16, State 3, Line 16 Invalid object name 'dbo.t1'.

any help?

the function,tables are all present in same schema

Answer 1

The syntax you are using is suitable for table-valued user-defined functions which return a table data type instead of just a scalar value.

Try this syntax instead:

SELECT t.id, t.amt, f.id 
FROM ttt t 
CROSS APPLY (SELECT dbo.t1(t.id) ) f(id)

Answer 2

its a scalar function. you need to call it like this

select t.id,t.amt,dbo.t1(t.id) id from ttt t 

For more info about types of functions in SQL Server, refer here