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

Giorgos Betsos · Accepted Answer · 2015-04-11 16:03:09Z

5

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)

edited Apr 11, 2015 at 16:03

answered Apr 9, 2015 at 6:39

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

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

4 Comments

Hare Rama Hare Krishna Over a year ago

I doing this select t.id as id1,t.amt as amt1,f.id as id2 from ttt t cross apply (select dbo.t1(t.id)) f but it gives error:

Msg 8155, Level 16, State 2, Line 16 No column name was specified for column 1 of 'f'. Msg 207, Level 16, State 1, Line 16 Invalid column name 'id'.

Giorgos Betsos Over a year ago

@HareRamaHareKrishna Take a more careful look at my post: I am using f(id), not just f.

ughai Over a year ago

@GiorgosBetsos - Nice answer, I am still doubtful on why the OP wants to use CROSS APPLY

Giorgos Betsos Over a year ago

@ughai I think he took up the syntax used for inline UDFs, e.g. from a site like this sql-server-helper.com/tips/…

ughai · Accepted Answer · 2015-04-09 06:59:24Z

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

edited Apr 9, 2015 at 6:59

answered Apr 9, 2015 at 6:31

ughai

9,9004 gold badges33 silver badges48 bronze badges

1 Comment

Giorgos Betsos Over a year ago

It's correct, just change f alias to id to match exactly the OP expression.

Collectives™ on Stack Overflow

Invalid object name error for a function used in sql server

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related