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??

Ian Preston · Accepted Answer · 2013-07-31 10:24:44Z

31

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.

answered Jul 31, 2013 at 10:24

Ian Preston

39.8k9 gold badges98 silver badges94 bronze badges

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

1 Comment

Mikhail Boyarsky Over a year ago

In my case issue was not using "dbo", but I don't really get those rules anyway.

AdaTheDev · Accepted Answer · 2013-07-31 10:33:24Z

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

t-clausen.dk · Accepted Answer · 2013-07-31 10:26:07Z

2

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')

answered Jul 31, 2013 at 10:26

t-clausen.dk

44.5k12 gold badges61 silver badges92 bronze badges

Comments

zombiejojo · Accepted Answer · 2013-07-31 10:41:49Z

1

Or if you do want a table function, try changing your function to be something like this - then you can use select * from...

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

    INSERT @outputTbl_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


GO

edited Jul 31, 2013 at 10:41

answered Jul 31, 2013 at 10:32

zombiejojo

751 silver badge6 bronze badges

Comments

4b0 · Accepted Answer · 2019-03-28 05:37:45Z

1

ALTER FUNCTION Isnulldate(@maxdate1 DATETIME, 
                          @maxdate2 DATETIME, 
                          @maxdate3 DATETIME) 
returns DATETIME 
AS 
  BEGIN 
      DECLARE @date DATETIME 

      IF @maxdate3 IS NOT NULL 
        BEGIN 
            SET @date=@maxdate3 

            RETURN @date 
        END 

      IF @maxdate2 IS NOT NULL 
        BEGIN 
            SET @date=@maxdate2 

            RETURN @date 
        END 

      IF @maxdate1 IS NOT NULL 
        BEGIN 
            SET @date=@maxdate1 

            RETURN @date 
        END 

      RETURN @date 
  END

## Execution ##

DECLARE @dateim DATETIME=Getdate() 
SELECT dbo.Isnulldate(NULL, NULL, @dateim)

edited Mar 28, 2019 at 5:37

4b0

22.4k30 gold badges97 silver badges143 bronze badges

answered Mar 28, 2019 at 5:26

Hari Lakkakula

3172 silver badges5 bronze badges

1 Comment

4b0 Over a year ago

While this code may solve the question, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion.

Collectives™ on Stack Overflow

Invalid Object Name Error in Function in SQL

5 Answers 5

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related