1 USE [MapCDE_1]  2 GO  3 /****** Object:  StoredProcedure [dbo].[sp_getLocations]    Script Date: 04/21/2014 11:27:20 ******/  4 SET ANSI_NULLS ON  5 GO  6 SET QUOTED_IDENTIFIER ON  7 GO  8   9  10  11 -- ============================================= 12 -- Author:        <Author,,Name> 13 -- Create date: <Create Date,,> 14 -- Description:    <Description,,> 15 -- ============================================= 16 ALTER PROCEDURE [dbo].[sp_getLocations] 17     @sMainType            VarChar(MAX), 18     @sSubRecomand        VarChar(30), 19     @sSubScenery        VarChar(30), 20     @sSubHotel            VarChar(30), 21     @sSubEatery            VarChar(100), 22     @sSearchCondition    VarChar(MAX), 23     @suserCondition     VarChar(MAX), 24     @sUserId            VarChar(30), 25     @iCount                int, 26     @iStartCount        int 27      28 AS 29     --Declare @sSql        VarChar(MAX) 30     Declare @sSql1        VarChar(MAX) 31     Declare @sSql2        VarChar(MAX) 32     Declare @sSql3        VarChar(MAX) 33     Declare @sSql4        VarChar(MAX) 34     Declare @sSql5        VarChar(MAX) 35     Declare @sSql6        VarChar(MAX) 36     Declare @sSql7        VarChar(MAX) 37     Declare @sSql8        VarChar(MAX) 38     Declare @sSql9        VarChar(MAX) 39     Declare @sSql10        VarChar(MAX) 40     Declare @sSql11        VarChar(MAX) 41     Declare @iGetCount    int 42 set @iGetCount = 0 43 Set @sSql1 = ‘ SELECT Convert(varchar,L.ID) ID, U.TOUR_TIME, L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID,‘‘7‘‘ AS HAVETOGO FROM dbo.TBL_LOCATIONINFO L INNER JOIN dbo.TBL_PERSONALMEDIAINFO U ON L.ID = U.LOCATIONID AND U.ISCATEGORYLINK = ‘‘1‘‘ WHERE 1 = 1 ‘ 44 Set @sSql2 = ‘ SELECT Convert(varchar,L.ID) ID, ISNULL(B.TOUR_TIME, ‘‘0‘‘) AS TOUR_TIME,    L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID,‘‘6‘‘ AS HAVETOGO FROM dbo.TBL_RECOMMENDINFO AS R INNER JOIN dbo.TBL_LOCATIONINFO AS L ON L.ID = R.LOCATIONID LEFT OUTER JOIN dbo.TBL_SCENERYSHOTS AS B ON L.ID = B.LOCATIONID WHERE 1=1 ‘ 45 Set @sSql3 = ‘ SELECT Convert(varchar,L.ID) ID,‘‘0‘‘ AS TOUR_TIME,    L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID ,‘‘5‘‘ AS HAVETOGO FROM dbo.TBL_LOCATIONINFO L WHERE L.LOCATIONTYPE = ‘‘6‘‘ ‘ 46 Set @sSql4 = ‘ SELECT Convert(varchar,L.ID) ID,‘‘0‘‘ AS TOUR_TIME,    L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID,‘‘4‘‘ AS HAVETOGO FROM dbo.TBL_RAILWAYSTATION B1 INNER JOIN dbo.TBL_LOCATIONINFO L ON B1.LOCATIONID = L.ID AND B1.DLEVEL = ‘‘1‘‘ WHERE L.LOCATIONTYPE = ‘‘7‘‘ ‘ 47 Set @sSql5 = ‘ SELECT Convert(varchar,L.ID) ID, ‘‘0‘‘ AS TOUR_TIME, L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID,‘‘3‘‘ AS HAVETOGO FROM dbo.TBL_LOCATIONINFO L WHERE L.LOCATIONTYPE = ‘‘9‘‘ ‘ 48 Set @sSql6 = ‘ SELECT Convert(varchar,L.ID) ID, S.TOUR_TIME AS TOUR_TIME, L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID ,S.HAVETOGO AS HAVETOGO FROM dbo.TBL_LOCATIONINFO L LEFT JOIN dbo.TBL_SCENERYSHOTS S ON L.ID = S.LOCATIONID WHERE L.LOCATIONTYPE = ‘‘5‘‘ ‘ 49 Set @sSql7 = ‘ SELECT Convert(varchar,L.ID) ID, ‘‘0‘‘ AS TOUR_TIME,    L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID,‘‘0‘‘ AS HAVETOGO FROM dbo.TBL_LOCATIONINFO L LEFT JOIN dbo.TBL_HOTELS S ON L.ID = S.LOCATIONID WHERE L.LOCATIONTYPE = ‘‘3‘‘ ‘ 50 Set @sSql8 = ‘ SELECT Convert(varchar,L.ID) ID, ‘‘0‘‘ AS TOUR_TIME,    L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID,‘‘0‘‘ AS HAVETOGO FROM dbo.TBL_LOCATIONINFO L LEFT JOIN dbo.TBL_EATRY S ON L.ID = S.LOCATIONID WHERE L.LOCATIONTYPE = ‘‘2‘‘ ‘ 51 Set @sSql9 = ‘ SELECT Convert(varchar,L.ID) ID, ‘‘0‘‘ AS TOUR_TIME,    L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID ,‘‘0‘‘ AS HAVETOGO FROM dbo.TBL_LOCATIONINFO L WHERE L.LOCATIONTYPE = ‘‘4‘‘ ‘ 52 Set @sSql10 = ‘ SELECT (‘‘_‘‘+Convert(varchar,L.ID)) as ID , ‘‘0‘‘ AS TOUR_TIME, L.COUNTRYNAME as COUNTRY, L.PROVINCENAME as PROVINCE, L.CITYNAME as CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID,‘‘0‘‘ AS HAVETOGO FROM dbo.TBL_USERLOC L LEFT JOIN dbo.TBL_USERS U ON L.COMPANYID = U.COMPANY_ID WHERE 1 = 1 ‘ 53 Set @sSql11 = ‘ SELECT Convert(varchar,L.ID) ID, ‘‘0‘‘ AS TOUR_TIME,    L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID ,‘‘0‘‘ AS HAVETOGO FROM dbo.TBL_LOCATIONINFO L WHERE L.LOCATIONTYPE = ‘‘8‘‘ ‘ 54  55  56  57 If @sUserId <> ‘‘ 58     Set @sSql1 = @sSql1 + ‘ And U.USERID in (‘ + @sUserId + ‘)‘ 59      60 If @sUserId <> ‘‘ 61     Set @sSql10 = @sSql10 + ‘ And U.ID in (‘ + @sUserId + ‘)‘ 62          63 If @sSubRecomand <> ‘‘ 64     Set @sSql2 = @sSql2 + ‘ And R.COMMENDLEV in (‘ + @sSubRecomand + ‘)‘ 65      66 If @sSubHotel <> ‘‘ 67     Set @sSql7 = @sSql7 + ‘ And S.STAR in (‘ + @sSubHotel + ‘)‘ 68  69 If @sSubScenery <> ‘‘ 70     Set @sSql6 = @sSql6 + ‘ And S.HAVETOGO in (‘ + @sSubScenery + ‘) ‘ 71  72 If @sSubEatery <> ‘‘ 73     Set @sSql8 = @sSql8 + ‘ And S.DTYPE in (‘ + @sSubEatery + ‘)‘ 74  75 If     @iGetCount < @iCount 76     Begin 77     EXEC (‘Insert into  TBL_TEMPLOCATION ‘ + @sSql10+ @suserCondition + @sSearchCondition) 78          79         Set @iGetCount = @iGetCount + @@ROWCOUNT 80     End 81  82 If CHARINDEX(‘0‘, @sMainType) > 0 And @iGetCount < @iCount 83     Begin 84         --Set @sSQL = ‘Insert into  TBL_TEMPLOCATION ‘ + @sSql1 + ‘ WHERE ‘ + @sSearchCondition 85         EXEC (‘Insert into TBL_TEMPLOCATION ‘ + @sSql1 + @sSearchCondition) 86         Set @iGetCount = @iGetCount + @@ROWCOUNT 87     End 88  89  90 If CHARINDEX(‘1‘, @sMainType) > 0 And @iGetCount < @iCount 91     Begin 92         EXEC (‘Insert into  TBL_TEMPLOCATION ‘ + @sSql2 + @sSearchCondition +  93             ‘And Convert(varchar,L.ID) not in (SELECT ID FROM TBL_TEMPLOCATION )‘) 94  95         Set @iGetCount = @iGetCount + @@ROWCOUNT 96     End 97  98 If CHARINDEX(‘6‘, @sMainType) > 0 And @iGetCount < @iCount 99     Begin100         EXEC (‘Insert into  TBL_TEMPLOCATION ‘ + @sSql3 + @sSearchCondition + ‘101         And Convert(varchar,L.ID) not in (SELECT ID FROM TBL_TEMPLOCATION )‘)102         103         Set @iGetCount = @iGetCount + @@ROWCOUNT104     End105 106 If CHARINDEX(‘7‘, @sMainType) > 0 And @iGetCount < @iCount107     Begin108         EXEC (‘Insert into  TBL_TEMPLOCATION ‘ + @sSql4 + @sSearchCondition + ‘109         And Convert(varchar,L.ID) not in (SELECT ID FROM TBL_TEMPLOCATION )‘)110         111         Set @iGetCount = @iGetCount + @@ROWCOUNT112     End113 114 If CHARINDEX(‘9‘, @sMainType) > 0 And @iGetCount < @iCount115     Begin116         EXEC (‘Insert into  TBL_TEMPLOCATION ‘ + @sSql5 + @sSearchCondition + ‘117         And Convert(varchar,L.ID) not in (SELECT ID FROM TBL_TEMPLOCATION )‘)118         119         Set @iGetCount = @iGetCount + @@ROWCOUNT120     End    121 122 123 If CHARINDEX(‘5‘, @sMainType) > 0 And @iGetCount < @iCount124     Begin125     --select ‘Insert into  TBL_TEMPLOCATION ‘ + @sSql6 + ‘ And ‘ + @sSearchCondition + ‘126     --    And L.ID not in (SELECT ID FROM TBL_TEMPLOCATION )‘127         EXEC (‘Insert into  TBL_TEMPLOCATION ‘ + @sSql6 + @sSearchCondition + ‘128         And Convert(varchar,L.ID) not in (SELECT ID FROM TBL_TEMPLOCATION ) order by S.HAVETOGO desc‘)129         130         Set @iGetCount = @iGetCount + @@ROWCOUNT131     End132 133 If CHARINDEX(‘3‘, @sMainType) > 0 And @iGetCount < @iCount134     Begin135     --select ‘Insert into  TBL_TEMPLOCATION ‘ + @sSql7 + ‘ And ‘ + @sSearchCondition + ‘136     --    And L.ID not in (SELECT ID FROM TBL_TEMPLOCATION )‘137         EXEC (‘Insert into  TBL_TEMPLOCATION ‘ + @sSql7 + @sSearchCondition + ‘138         And Convert(varchar,L.ID) not in (SELECT ID FROM TBL_TEMPLOCATION )‘)139         140         Set @iGetCount = @iGetCount + @@ROWCOUNT141     End142 143 If CHARINDEX(‘2‘, @sMainType) > 0 And @iGetCount < @iCount144     Begin145         EXEC (‘Insert into  TBL_TEMPLOCATION ‘ + @sSql8 + @sSearchCondition + ‘146         And Convert(varchar,L.ID) not in (SELECT ID FROM TBL_TEMPLOCATION )‘)147         148         Set @iGetCount = @iGetCount + @@ROWCOUNT149     End150 151 If CHARINDEX(‘4‘, @sMainType) > 0 And @iGetCount < @iCount152     Begin153         EXEC (‘Insert into  TBL_TEMPLOCATION ‘ + @sSql9 + @sSearchCondition + ‘154         And Convert(varchar,L.ID) not in (SELECT ID FROM TBL_TEMPLOCATION )‘)155         156         Set @iGetCount = @iGetCount + @@ROWCOUNT157     End158 If CHARINDEX(‘8‘, @sMainType) > 0 And @iGetCount < @iCount159 Begin160     EXEC (‘Insert into  TBL_TEMPLOCATION ‘ + @sSql11 + @sSearchCondition + ‘161     And Convert(varchar,L.ID) not in (SELECT ID FROM TBL_TEMPLOCATION )‘)162     163     Set @iGetCount = @iGetCount + @@ROWCOUNT164 End165 166 --Exec (‘SELECT * FROM TBL_TEMPLOCATION167 --        DELETE FROM TBL_TEMPLOCATION‘)168 --        --DELETE FROM TBL_TEMPLOCATION169 Exec (‘SELECT k.* from(SELECT ROW_NUMBER()over(order by  HAVETOGO desc ) rownumber, ID, TOUR_TIME,170         COUNTRY, PROVINCE, CITY, LONGITUDE, LATITUDE, LOCATIONNAME, LOCATIONTYPE, ADDRESS,COMPANYID,HAVETOGO171     FROM TBL_TEMPLOCATION)k  where k.rownumber> ‘+ @iStartCount +‘ And k.rownumber <= ‘+ @iCount +‘;172     DELETE FROM TBL_TEMPLOCATION;‘)