USE[MLS] GO /**//****** Object: UserDefinedFunction [dbo].[SplitWords] Script Date: 02/25/2008 15:55:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATEFUNCTION[dbo].[SplitWords](@textvarchar(8000)) RETURNS@wordsTABLE (pos smallintprimarykey, value varchar(8000)) AS BEGIN DECLARE@possmallint, @ismallint, @jsmallint, @svarchar(8000) SET@pos=1 WHILE@pos<=LEN(@text) BEGIN SET@i=CHARINDEX('', @text, @pos) SET@j=CHARINDEX(',', @text, @pos) IF@i>0OR@j>0 BEGIN IF@i=0OR (@j>0AND@j<@i) SET@i=@j IF@i>@pos BEGIN -- @i now holds the earliest delimiter in the string SET@s=SUBSTRING(@text, @pos, @i-@pos) INSERTINTO@words VALUES (@pos, @s) END SET@pos=@i+1 WHILE@pos<LEN(@text) ANDSUBSTRING(@text, @pos, 1) IN ('', ',') SET@pos=@pos+1 END ELSE BEGIN INSERTINTO@words VALUES (@pos, SUBSTRING(@text, @pos, LEN(@text) -@pos+1)) SET@pos=LEN(@text) +1 END END RETURN END
USE[MLS] GO /**//****** Object: StoredProcedure [dbo].[SaveLocationSearch] Script Date: 02/25/2008 15:56:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATEPROCEDURE[dbo].[SaveLocationSearch] @titlenvarchar(50), @citynvarchar(50), @regionnvarchar(50), @countrynvarchar(50), @longitudenvarchar(50), @latitudenvarchar(50), @subcategoryIDint, @phonenvarchar(50) AS DECLARE@Counterint BEGIN DECLARE@ErrorCodeint SELECT@ErrorCode=0 DECLARE@TranStartedbit SELECT@TranStarted=0 IF(@@TRANCOUNT=0) BEGIN BEGINTRANSACTION SELECT@TranStarted=1 END ELSE SELECT@TranStarted=0 UPDATE testcounter SET[count]=[count]+1 SELECT@Counter=COUNT(*) FROM nearby WHERE longitude =@longitude AND latitude =@latitude AND title =@title AND city =@city AND region =@region AND country =@country AND subcategoryID =@subcategoryID AND phone =@phone IF (@Counter=0) BEGIN INSERTINTO nearby ( title, city, region, country, longitude, latitude, subcategoryid, phone ) VALUES ( @title, @city, @region, @country, @longitude, @latitude, @subcategoryid, @phone ) END IF(@@ERROR<>0) BEGIN SELECT@ErrorCode=-1 GOTO Cleanup END IF(@TranStarted=1) BEGIN SELECT@TranStarted=0 COMMITTRANSACTION END Cleanup: IF(@TranStarted=1) BEGIN SET@TranStarted=0 ROLLBACKTRANSACTION END RETURN@ErrorCode END
USE[MLS] GO /**//****** Object: StoredProcedure [dbo].[SearchNearBy] Script Date: 02/25/2008 15:56:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATEPROCEDURE[dbo].[SearchNearBy] @categoriesnvarchar(2000), @longitudeDifferencenvarchar(50), @latitudeDifferencenvarchar(50), @longitudenvarchar(50), @latitudenvarchar(50) AS DECLARE@wordsASTABLE ( pos smallintprimarykey, [value]varchar(8000) ) DECLARE@subcategoriesASTABLE ( subcategoryID int ) BEGIN DECLARE@ErrorCodeint SELECT@ErrorCode=0 DECLARE@TranStartedbit SELECT@TranStarted=0 IF(@@TRANCOUNT=0) BEGIN BEGINTRANSACTION SELECT@TranStarted=1 END ELSE SELECT@TranStarted=0 INSERTINTO@words(pos,[value]) SELECT*FROM dbo.SplitWords(@categories) DECLARE c CURSORFOR SELECT[value]FROM@words DECLARE@categoryASnvarchar(100) OPEN c LOOP: FETCHNEXTFROM c INTO @category IF@@FETCH_STATUS=-1GOTO END_LOOP /**//* EOF */ IF@@FETCH_STATUS=-2GOTO LOOP /**//* DELETED ROW */ DECLARE@categoryIDASint SELECT@categoryID= categoryid FROM category where Description like@category INSERTINTO@subcategories(subcategoryID) SELECT subcategoryID FROM subcategory WHERE categoryid =@categoryid GOTO LOOP END_LOOP: CLOSE c DEALLOCATE c SELECT* FROM nearby WHERE (longitude<>'null') AND (latitude<>'null') AND ( CAST(longitude ASDECIMAL (18,8)) BETWEEN (CAST(@longitudeASDECIMAL (18,8))-CAST(@longitudeDifferenceASDECIMAL (18,8))) AND (CAST(@longitudeASDECIMAL (18,8))+CAST(@longitudeDifferenceASDECIMAL (18,8))) ) AND ( CAST(latitude ASDECIMAL (18,8)) BETWEEN (CAST(@latitudeASDECIMAL (18,8))-CAST(@latitudeDifferenceASDECIMAL (18,8))) AND (CAST(@latitudeASDECIMAL (18,8))+CAST(@latitudeDifferenceASDECIMAL (18,8))) ) AND subcategoryID IN ( SELECT subcategoryID FROM@subcategories ) IF(@@ERROR<>0) BEGIN SELECT@ErrorCode=-1 GOTO Cleanup END IF(@TranStarted=1) BEGIN SELECT@TranStarted=0 COMMITTRANSACTION END Cleanup: IF(@TranStarted=1) BEGIN SET@TranStarted=0 ROLLBACKTRANSACTION END RETURN@ErrorCode END
USE[MLS] GO /**//****** Object: StoredProcedure [dbo].[SearchSurrounding] Script Date: 02/25/2008 15:56:29 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATEPROCEDURE[dbo].[SearchSurrounding] @longitudenvarchar(50), @latitudenvarchar(50), @longitudeDifferencenvarchar(50), @latitudeDifferencenvarchar(50), @minPricemoney, @maxPricemoney, @bedstinyint, @bathsdecimal(7,3) AS BEGIN DECLARE@ErrorCodeint SELECT@ErrorCode=0 DECLARE@TranStartedbit SELECT@TranStarted=0 IF(@@TRANCOUNT=0) BEGIN BEGINTRANSACTION SELECT@TranStarted=1 END ELSE SELECT@TranStarted=0 selectdistinct zipcode, ltrim(rtrim(mlsnumber)), SUBSTRING(mlsnumber,DATALENGTH(mlsnumber)-4,2), lprice, isnull(communityd,'unknown'), beds, baths, type1, isnull(Tarea,0), picturecount, (select longitude from mlstest where mls=mlsnumber), (select latitude from mlstest where mls=mlsnumber), address from V_RES_IDX_TESTING where mlsnumber IN ( SELECT mls FROM mlstest WHERE (longitude<>'null') AND ( CAST(longitude ASDECIMAL (18,8)) BETWEEN (CAST(@longitudeASDECIMAL (18,8))-CAST(@longitudeDifferenceASDECIMAL (18,8))) AND (CAST(@longitudeASDECIMAL (18,8))+CAST(@longitudeDifferenceASDECIMAL (18,8))) ) AND ( CAST(latitude ASDECIMAL (18,8)) BETWEEN (CAST(@latitudeASDECIMAL (18,8))-CAST(@latitudeDifferenceASDECIMAL (18,8))) AND (CAST(@latitudeASDECIMAL (18,8))+CAST(@latitudeDifferenceASDECIMAL (18,8))) ) ) AND ( lprice>=@minPriceAND lprice<=@maxPrice ) AND (beds>@beds) AND (baths>@baths) IF(@@ERROR<>0) BEGIN SELECT@ErrorCode=-1 GOTO Cleanup END IF(@TranStarted=1) BEGIN SELECT@TranStarted=0 COMMITTRANSACTION END Cleanup: IF(@TranStarted=1) BEGIN SET@TranStarted=0 ROLLBACKTRANSACTION END RETURN@ErrorCode END