Click here to Skip to main content
15,068,646 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
When I call an inline table value function I get this error:

"Invalid object name Reservations.ReservationDetails"

What I have tried:

This is my Inline Table Value Function:

SQL
DECLARE @IDMovies INT = Movies.IDMovies
CREATE FUNCTION [dbo].Reservations.ReservationDetails (@IDMovies INT)
RETURNS TABLE  
AS
RETURN  
         SELECT *
     FROM Movies m
     INNER JOIN Reservations r ON b.IDMovies = r.IDMovies
Posted
Updated 7-Nov-20 5:12am
v2

1 solution

Your function name cannot contain a . unless you escape it.
Database Identifiers - SQL Server | Microsoft Docs[^]

Give it a simpler name:
SQL
CREATE FUNCTION dbo.ReservationDetails (@IDMovies INT)
Or create it in a separate schema:
SQL
CREATE FUNCTION Reservations.ReservationDetails (@IDMovies INT)

If you really want to shoot yourself in the foot, escape the function name. You will also need to escape it every time you call it.
SQL
-- Don't do this!
CREATE FUNCTION [dbo].[Reservations.ReservationDetails] (@IDMovies INT)
   
Comments
xhon 6-Nov-20 10:40am
   
I got this other error message:
"Column names in each view or function must be unique. Column name 'IDMovies' in view or function 'ReservationDetails' is specified more than once."
Richard Deeming 6-Nov-20 10:43am
   
Don't use SELECT * FROM; specify the column list explicitly, and make sure you don't have any duplicate column names.

For example, both tables in your JOIN have a column called IDMovies.
xhon 6-Nov-20 10:51am
   
OK thanks, I'm gonna drop the table which contains the foreign key and create it again by naming the referring column differently...Or there is a way to avoid dropping the table? Can I use a variable for that column name inside the function maybe?
Richard Deeming 6-Nov-20 10:52am
   
You don't need to drop the table. Just specify an explicit list of columns in your SELECT statement.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900