Database

Create Procedure sp_Lock_Select
(
@Lis_key varchar(34) 
)
as 

if Exists(
SELECT Primary_ID, Boundary_area, Attrib_Code, Lis_Key, Func_Key, Actual_Extent, 
Improved_Value, Site_Value, Purchase_price, Purchase_date, prop_id, 
confidence_score, fin_ls_prediction, new_lis_key, new_func_key,
 Tshwane_Prediction, matchflag, source, flagtype, 
Locked FROM Property_mass WHERE (SUBSTRING(Lis_Key, 1, 4) = @Lis_key) 
AND (Locked IS NULL)
)
Update Property_Mass
set Locked = 1
WHERE (SUBSTRING(Lis_Key, 1, 4) = @Lis_key And Locked IS NULL) 

create Procedure Update_And_UnLock
(
@Tshwane_Prediction float ,
@Primary_ID int
)
as 
Update Property_Mass 
set Tshwane_Prediction = @Tshwane_Prediction
where Primary_ID = @Primary_ID

DECLARE @tmp TABLE
(
     CityName NVARCHAR(200)
)
  
INSERT INTO @tmp (CityName)
SELECT TOP 10 
    ISNULL(Cities_Localized.[Name], Cities.[Name])
FROM 
    Cities
LEFT JOIN 
    Cities_Localized 
    ON (Cities.CityID = Cities_Localized.CityID) AND 
       (Cities_Localized.Language  = 'en-US') 
WHERE 
    Cities.[Name] LIKE 'T%' OR 
    Cities_Localized.[Name] LIKE 'T%'
  
SELECT 
    CityName 
FROM
    @tmp
ORDER BY
    CityName

SELECT 
    [Name] AS CityName
FROM
    Cities
WHERE 
    [Name] LIKE 'T%'
  
UNION 
  
SELECT 
    [Name]
FROM
    Cities_Localized 
WHERE
    Cities_Localized.Language  = 'en-US' AND
    Cities_Localized.[Name] LIKE 'T%'
  
ORDER BY
    CityName

--Get top 10 city names that do NOT have a localised version.
select top 10 C.CityId, C.Name
  into #tmp
  from Cities C
  left outer join Cities_Localization CL
    on CL.CityID = C.CityID
    and CL.Language = 'en-US'
  where C.Name like 'T%'
  and CL.CityID is null
  order by C.Name

--Add the top 10 localised city names.
insert into #tmp (CityID, Name)
  select top 10 CL.CityID, CL.Name
  from Cities_Localization CL
  where CL.Name like 'T%'
  and CL.Language = 'en-US'
  order by CL.Name

--Return top 10 cities (localised or not).
select top 10 #tmp.Name
  from #tmp
  order by Name