Invalid Object Name Error in Function in SQL

Question

I have following function defined

alter  FUNCTION [dbo].[GetXMLValues](@business_id int, @id varchar(30))
RETURNS varchar(30)
AS
BEGIN

declare @xmlValue varchar(30)

set @xmlValue =  (SELECT top 1000  T.Content.value('(/XmlDataPairDocument/dataitem[@id=sql:variable("@id")]/@value)[1]', 'VARCHAR(100)')
                    FROM tblApplications T where t.business_id =@business_id)


return @xmlValue




END

WHen i hit F5 command Executes Successfully/...

but when i try to execute it using following query :

select * from [GetXMLValues](1,'sadfj')

it shows an error saying : Invalid object name 'GetXMLValues'.

what is the reason ? and what is error??

Answer 1

This is a Scalar function, not a Table-Valued function.

select dbo.[GetXMLValues](1,'sadfj')

should work.

You can't treat this like a table, i.e. select * ..., you need to just select the result directly as above.

See Types of Functions for more details.

Answer 2

As mentioned by t-clausen.dk and Ian Preston, it's because you have a Scalar function and not a table valued function.

I just wanted to extend on t-clausen.dk's post which switches your function to a multi-statement table valued function. I would take this a step further and actually use an inline table valued function:

ALTER FUNCTION [dbo].[GetXMLValues](@business_id int, @id varchar(30))
RETURNS TABLE
AS
RETURN (
    SELECT top 1000  T.Content.value('(/XmlDataPairDocument/dataitem[@id=sql:variable("@id")]/@value)[1]',     'VARCHAR(100)')
    FROM tblApplications T where t.business_id =@business_id
)

Which you then use in the same way:

select xmlValue from dbo.[GetXMLValues](1,'sadfj')

Check out: Query performance and multi-statement table valued functions

Answer 3

your function is not returning a table, it is returning a varchar(30). The correct syntax to use your function would be:

select [dbo].[GetXMLValues](1,'sadfj')

Try function this instead:

ALTER FUNCTION [dbo].[GetXMLValues](@business_id int, @id varchar(30))
RETURNS @t table (xmlValue varchar(30))
AS
BEGIN

insert @t (xmlValue)
SELECT top 1000  T.Content.value('(/XmlDataPairDocument/dataitem[@id=sql:variable("@id")]/@value)[1]', 'VARCHAR(100)')
FROM tblApplications T where t.business_id =@business_id

return
end

Then you can call your function this way:

select xmlValue from dbo.[GetXMLValues](1,'sadfj')