Your manager has asked you to create a stored procedure for an application the b

Question

Your manager has asked you to create a stored procedure for an application the business is developing. For the application, management has requested that when a "City" is selected, it returns with the corresponding region. By integrating code, the business will be able to return results quickly for users. Use the Address, StateProvince, and SalesTerritory tables and name the procedure "GetRegion." TIP: Use GO. But when you have code USE AdventureWorks2014 GO ALTER PROC GetRegion @City nvarchar(30) = Null AS IF @City IS NOT NULL SELECT Person.Address.City, Sales.SalesTerritory.CountryRegionCode From Sales.SalesTerritory JOIN Person.StateProvince ON Sales.SalesTerritory.TerritoryID = Person.StateProvince.TerritoryID JOIN Person.Address ON Person.StateProvince.StateProvinceID = Person.Address.StateProvinceID WHERE Person.Address.City like @City + '%'; ELSE SELECT Person.Address.City, Sales.SalesTerritory.CountryRegionCode From Sales.SalesTerritory JOIN Person.StateProvince Msg 4104, Level 16, State 1, Procedure GetRegion, Line 15 The multi-part identifier "Person.Address.City" could not be bound.

Explanation / Answer

alter PROC GetRegion @City nvarchar(30) AS SELECT Person.Address.City, Sales.SalesTerritory.CountryRegionCode From Sales.SalesTerritory JOIN Person.StateProvince ON Sales.SalesTerritory.TerritoryID = Person.StateProvince.TerritoryID JOIN Person.Address ON Person.StateProvince.StateProvinceID = Person.Address.StateProvinceID WHERE Person.Address.City like @City + '%';

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.