2

I have this function get_department. Inside the function there is a function call for get_po_amount.

The get_po_amount function doesn't exist anymore, so when I try to create the Main function get_Department, I get the error "invalid object name get_po_amount".

Is there any way that I could create the function by not creating the function inside.

I have the same issue with views also. Calling a view inside another view.

I created these functions just to show an example, I need to do this on a large database.

CREATE FUNCTION [dbo].[GET_DEPARTEMNET]
(
    @DEPTNO NVARCHAR(100)
    
)
    RETURNS TABLE 
AS 
RETURN 
   (    
        SELECT * FROM  [dbo].[dept] WHERE DEPTNO=(SELECT [dbo].[GET_PO_AMOUNT](@DEPTNO)))
GO
4
  • This seems really confusing. If you are passing in the department number, why not just use that in the first query, SELECT * FROM [dbo].[dept] WHERE DEPTNO=@DEPTNO, instead of passing it into the subquery, which is either really poorly named or not returning the right thing
    – Kevin
    Nov 30, 2022 at 15:30
  • That's y said I gave the function just as an example. Dec 1, 2022 at 5:28
  • Do you see why using fake code that is confusing might make it hard for people to answer your question?
    – Kevin
    Dec 1, 2022 at 15:30
  • Its confusing because you didn't read what I wrote. You should have read it first completely .There are other people who read it right and answered . They didn't see anything confusing . Dec 2, 2022 at 4:29

2 Answers 2

5

These things are strongly typed, in the sense that when you create a function or view, SQL Server needs to know what it returns. Having it use some non-existent object prohibits this strong typing, so that isn't possible.

0

You might need to find the hierarchy order of views and function to create like that .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.