E
E
ellz2019-02-13 22:56:58
Transact SQL
ellz, 2019-02-13 22:56:58

Why does procedure contains an unresolved reference to an object occur?

There is a procedure:

CREATE PROCEDURE [dbo].[GetToursList](	
   @toCity nvarchar(30) = null
   , @toCountry nvarchar(30) = null
   ,  @departureDate date
   , @amountNights int
   , @amountAdults int)
AS
BEGIN
--DECLARE @toCityID int  
--DECLARE @toCuntry int
--SET @toCityID = (SELECT Cities.Id FROM dbo.Cities WHERE Cities.Name = @toCity)
--SET @toCuntry = (SELECT Countrys.Id from dbo.Countrys where Countrys.Name = @toCuntry)

SELECT 
hotels.Name as HotelName
,cities.Name as CityName
,countrys.Name as CounrtyName
,Tours.Price
,hotels.Rating as Stars
,hotels.Line as Line
,hotels.DistanceToAirport as DistToAirport
,hotels.DistanceToBeach as DistTobeach
FROM dbo.Tours 
INNER JOIN dbo.Countrys as countrys
ON CountryID = countrys.Id
INNER JOIN dbo.Cities as cities
ON CityID = cities.Id
INNER JOIN dbo.Hotels as hotels 
ON HotelID = hotels.Id
WHERE
(@toCountry is null or(CountryID = (select Countrys.Id from dbo.Countrys where Countrys.Name = @toCountry)))--подчеркивает CountryID
and
(@toCity is null or(CityID =       (select Cities.Id   from dbo.Cities   where   Cities.Name = @toCity)))
END

In place or(CountryID VS shows error -
Procedure: [dbo].[GetToursList] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[Cities].[CountryID], [dbo].[Countrys].[CountryID], [dbo]. [Hotels].[CountryID] or [dbo].[Tours].[CountryID]. dbo.GetToursListByCity

Tried to do like this:
(@toCountry is null or(CountryID = (select countrys.Id from countrys where countrys.Name = @toCountry)))

but didn't help.
The error disappears if you remove this piece of code -
INNER JOIN dbo.Cities as cities
ON CityID = cities.Id

But I need join to cities. I really don't understand how this is related.
Tables:

Tours:
CREATE TABLE [dbo].[Tours] (
    [Id]              INT            IDENTITY (1, 1) NOT NULL,
    [HotelID]         INT            NOT NULL,
    [FromDate]        DATE           NOT NULL,
    [ToDate]          DATE           NOT NULL,
    [Price]           INT            NOT NULL,
    [CityID]          INT            NOT NULL,
    [CountryID]       INT            NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Cities:
CREATE TABLE [dbo].[Cities] (
    [Id]        INT           IDENTITY (1, 1) NOT NULL,
    [Name]      NVARCHAR (90) NOT NULL,
    [CountryID] INT           NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Countrys:
CREATE TABLE [dbo].[Countrys] (
    [Id]   INT           IDENTITY (1, 1) NOT NULL,
    [Name] NVARCHAR (90) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

Hotels:
CREATE TABLE [dbo].[Hotels] (
    [Id]                INT            IDENTITY (1, 1) NOT NULL,
    [City]              INT            NOT NULL,
    [Rating]            INT            NOT NULL,
    [Name]              NVARCHAR (60)  NOT NULL,
    [DistanceToBeach]   INT            NULL,
    [Line]              INT            NULL,
    [DistanceToAirport] INT            NOT NULL,
    [Discription]       NVARCHAR (MAX) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);


The logic is this: if the input value "@toCity" == null, then the search goes by country, if "@toCity" != null, then by city.

Answer the question

In order to leave comments, you need to log in

1 answer(s)
K
Konstantin Tsvetkov, 2019-02-13
@ellz

Remove all unnecessary dbo.Before the field name, always indicate from which table.
The error is that it is CountryIDnot defined from which table: already specified [Tours]or from a linked [Cities].
Additionally:

[Tours]
    [HotelID]         INT            NOT NULL
    [CityID]          INT            NOT NULL,
    [CountryID]       INT            NOT NULL,
Can a hotel be without a city, a city without a country? No. Mistake.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question