?? usersp_chwl_searchinfo_key.txt
字號:
-- 信息搜索存儲過程, 支持優推
-- 根據條件進行信息搜索
-- 注意參數是按照順序給出, 沒有按照名稱
IF EXISTS(SELECT name FROM sysobjects WHERE name = 'usersp_chwl_searchinfo_Key' AND type = 'P')
DROP PROCEDURE usersp_chwl_searchinfo_Key
go
-- 開始創建
create procedure usersp_chwl_searchinfo_Key
@nameCValue varchar(300), -- 名稱, 空表示不使用此條件
@typeCValue varchar(300), -- 類別, 空表示不使用此條件
@addrCValue varchar(300), -- 地址, 空表示不使用此條件
@specCValue varchar(300), -- 特色, 空表示不使用此條件
@directposCValue varchar(300), -- 方位, 空表示不使用此條件
@phoneCValue varchar(100), -- 電話, 空表示不使用此條件
@memoCValue varchar(300), -- 備注, 空表示不使用此條件
-- @keyCValue varchar(200) --關鍵字，空表示不使用此條件
with ENCRYPTION
as
begin
set nocount on
-- 定義查詢所輸入的值
declare @nameValue varchar(300) -- 名稱
declare @typeValue varchar(300) -- 類別
declare @addrValue varchar(300) -- 地址
declare @specValue varchar(300) -- 特色
declare @directPosValue varchar(300) -- 方位
declare @phoneValue varchar(100) -- 電話
declare @memoValue varchar(300) -- 方位
declare @keyValue varchar(300) -- 關鍵字
declare @sqlCondition varchar(2000) -- 生成的查詢條件
set @sqlCondition = ''
-- 先去空格得到輸入的查詢條件值
set @nameValue = LTRIM(RTRIM(@nameCValue))
set @typeValue = LTRIM(RTRIM(@typeCValue))
set @addrValue = LTRIM(RTRIM(@addrCValue))
set @specValue = LTRIM(RTRIM(@specCValue))
set @directPosValue = LTRIM(RTRIM(@directPosCValue))
set @phoneValue = LTRIM(RTRIM(@phoneCValue))
set @memoValue = LTRIM(RTRIM(@memoCValue))
-- set @keyValue = LTRIM(RTRIM(@keyCValue))
set @keyValue = LTRIM(RTRIM(@nameCValue))
-- 對查詢條件值進行單引號替換, 避免一些
set @nameValue = REPLACE(@nameValue,'''','''''')
set @typeValue = REPLACE(@typeValue,'''','''''')
set @addrValue = REPLACE(@addrValue,'''','''''')
set @specValue = REPLACE(@specValue,'''','''''')
set @directPosValue = REPLACE(@directPosValue,'''','''''')
set @phoneValue = REPLACE(@phoneValue,'''','''''')
set @memoValue = REPLACE(@memoValue,'''','''''')
set @keyValue = REPLACE(@keyValue,'''','''''')
-- 定義查詢條件所用的列的名稱
declare @fieldID varchar(50)
declare @fieldUnionName varchar(50)
declare @fieldName varchar(50)
declare @fieldAddr varchar(50)
declare @fieldPhone varchar(50)
declare @fieldType varchar(50)
declare @fieldSpec varchar(50)
declare @fieldTel varchar(50)
declare @fieldMemo varchar(50)
declare @fieldPosX varchar(50)
declare @fieldPosY varchar(50)
declare @fieldKey varchar(50)
set @fieldID = 'F_ID'
set @fieldUnionName = 'F_UNIONNAME'
set @fieldName = 'F_NAME'
set @fieldAddr = 'F_ADDR'
set @fieldType = 'F_TYPE'
set @fieldSpec = 'F_SPEC'
set @fieldTel = 'F_TEL'
set @fieldMemo = 'F_MEMO'
set @fieldPosX = 'F_POSX'
set @fieldPosY = 'F_POSY'
set @fieldKey = 'F_KEY_NAME'
-- 定義查詢條件所用的表的名稱
declare @queryTableName varchar(500)
set @queryTableName = 'V_POI_INFO_GI'+
' left join T_POI_INFO_KEY on F_POIID=V_POI_INFO_GI.F_ID '
set @querytableName=@queryTableName+' left join T_POI_INFO_YT T_YT1 on F_YT1=T_YT1.F_YTID'
+' left join T_POI_INFO_YT T_YT2 on F_YT2=T_YT2.F_YTID'
+' left join T_POI_INFO_YT T_YT3 on F_YT3=T_YT3.F_YTID'
+' left join T_POI_INFO_YT T_YT4 on F_YT4=T_YT4.F_YTID '
+' left join T_POI_INFO_GRADE on F_GRADE=F_GRADE_ID '
-- 定義結果列
declare @resultFields nvarchar(500)
set @resultFields = ' ' + @fieldID + ',' + @fieldName + ',' + @fieldAddr + ',' +
@fieldTel + ',' + @fieldSpec + ',' +
@fieldMemo + ',' + @fieldPosX + ',' +
@fieldPosY + ', F_DETAIL, F_TLINE,F_PTLINE,F_ENV,F_LEVEL,F_COST, F_AGIO,F_KEY_NAME'
+',T_YT1.F_YTNAME AS YT_NAME1, F_YT1_DESC,T_YT2.F_YTNAME AS YT_NAME2,F_YT2_DESC,
T_YT3.F_YTNAME AS YT_NAME3, F_YT3_DESC,T_YT4.F_YTNAME AS YT_NAME4,F_YT4_DESC,
F_GRADE,F_GRADE_NAME,V_POI_INFO_GI.F_START_DATE,V_POI_INFO_GI.F_END_DATE'
-- 準備查詢SQL條件
-- 名稱
if (0 < len(@nameValue))
begin
if (0 < len(@sqlCondition)) set @sqlCondition = @sqlCondition + ' and '
set @sqlCondition = @sqlCondition + '(' + @fieldUnionName + ' like ''%' + @nameValue + '%'')'
end
-- 類別
if (0 < len(@typeValue))
begin
if (0 < len(@sqlCondition)) set @sqlCondition = @sqlCondition + ' and '
set @sqlCondition = @sqlCondition + ' (' + @fieldID + ' in (SELECT DISTINCT F_POIID FROM V_POI_INFOSEL WHERE F_TYPE=''' + @typeValue + '''' + ') ' + ')'
end
-- 地址
if (0 < len(@addrValue))
begin
if (0 < len(@sqlCondition)) set @sqlCondition = @sqlCondition + ' and '
set @sqlCondition = @sqlCondition + '(' + @fieldAddr + ' like ''%' + @addrValue + '%'')'
end
-- 電話
if (0 < len(@phoneValue))
begin
if (0 < len(@sqlCondition)) set @sqlCondition = @sqlCondition + ' and '
set @sqlCondition = @sqlCondition + '(' + @fieldTel + ' like ''%' + @phoneValue + '%'')'
end
-- 特色
if (0 < len(@specValue))
begin
if (0 < len(@sqlCondition)) set @sqlCondition = @sqlCondition + ' and '
set @sqlCondition = @sqlCondition + '(' + @fieldSpec + ' like ''%' + @specValue + '%'')'
end
-- 方位
if (0 < len(@directPosValue))
begin
if (0 < len(@sqlCondition)) set @sqlCondition = @sqlCondition + ' and '
-- 對于"城東""城西""城南""城北""城中心"這幾個特殊的方位, 需要進行特殊處理
-- 具體來講, 如果是這幾個, 則根據電話號碼來區別
if ('城東' = @directPosValue)
begin
set @sqlCondition = @sqlCondition + '(' + @fieldTel + ' like ''%' + '028-84' + '%'')'
end
else if ('城西' = @directPosValue)
begin
-- 城西有兩個頭
set @sqlCondition = @sqlCondition + '('
set @sqlCondition = @sqlCondition + '(' + @fieldTel + ' like ''%' + '028-87' + '%'')'
set @sqlCondition = @sqlCondition + ' or '
set @sqlCondition = @sqlCondition + '(' + @fieldTel + ' like ''%' + '028-81' + '%'')'
set @sqlCondition = @sqlCondition + ')'
end
else if ('城南' = @directPosValue)
begin
set @sqlCondition = @sqlCondition + '(' + @fieldTel + ' like ''%' + '028-85' + '%'')'
end
else if ('城北' = @directPosValue)
begin
set @sqlCondition = @sqlCondition + '(' + @fieldTel + ' like ''%' + '028-83' + '%'')'
end
else if ('城中心' = @directPosValue)
begin
set @sqlCondition = @sqlCondition + '(' + @fieldTel + ' like ''%' + '028-86' + '%'')'
end
else
begin
-- 由于方位里面擴展了可以通過坐標框選來查詢, 所以下面還需要檢查是否是坐標框選
-- 如果是框選的坐標, 其格式為"地圖: 4781.765,3575.973,5191.345,3277.824", 即以"地圖:"開頭
-- 后面跟上4個以逗號相隔的數字(left, top, right, bottom)
declare @strMapStyleHeader varchar(20)
declare @isMapStyle int
set @isMapStyle = 0
set @strMapStyleHeader = '地圖:'
if (len(@directPosValue) > len(@strMapStyleHeader))
begin
if (SUBSTRING(@directPosValue,1,len(@strMapStyleHeader)) = @strMapStyleHeader)
begin
set @isMapStyle = 1
end
end
if (1 = @isMapStyle)
begin
--按照坐標位置來查
-- 解出傳來的坐標范圍
declare @tmpPosEntireString varchar(300)
declare @tmpPosString varchar(300)
declare @lFindPos int
declare @left float
declare @top float
declare @right float
declare @bottom float
declare @parsePosOK int
set @parsePosOK = 1
set @tmpPosEntireString = right(@directPosValue, len(@directPosValue) - len(@strMapStyleHeader))
-- left
if (1 = @parsePosOK)
begin
set @lFindPos = CHARINDEX(',', @tmpPosEntireString)
if (0 = @lFindPos)
begin
set @parsePosOK = 0
end
else
begin
set @tmpPosString = left(@tmpPosEntireString, @lFindPos - 1)
set @tmpPosEntireString = right(@tmpPosEntireString, len(@tmpPosEntireString) - @lFindPos)
set @left = cast(@tmpPosString as float)
end
end
-- top
if (1 = @parsePosOK)
begin
set @lFindPos = CHARINDEX(',', @tmpPosEntireString)
if (0 = @lFindPos)
begin
set @parsePosOK = 0
end
else
begin
set @tmpPosString = left(@tmpPosEntireString, @lFindPos - 1)
set @tmpPosEntireString = right(@tmpPosEntireString, len(@tmpPosEntireString) - @lFindPos)
set @top = cast(@tmpPosString as float)
end
end
-- right
if (1 = @parsePosOK)
begin
set @lFindPos = CHARINDEX(',', @tmpPosEntireString)
if (0 = @lFindPos)
begin
set @parsePosOK = 0
end
else
begin
set @tmpPosString = left(@tmpPosEntireString, @lFindPos - 1)
set @tmpPosEntireString = right(@tmpPosEntireString, len(@tmpPosEntireString) - @lFindPos)
set @right = cast(@tmpPosString as float)
end
end
-- bottom
if (1 = @parsePosOK)
begin
-- 最后一個, 沒有逗號了
set @bottom = cast(@tmpPosEntireString as float)
end
-- 如果上面的解析沒有問題
if (1 = @parsePosOK)
begin
declare @minX float
declare @minY float
declare @maxX float
declare @maxY float
if (@left < @right)
begin
set @minX = @left
set @maxX = @right
end
else
begin
set @minX = @right
set @maxX = @left
end
if (@top < @bottom)
begin
set @minY = @top
set @maxY = @bottom
end
else
begin
set @minY = @bottom
set @maxY = @top
end
set @sqlCondition = @sqlCondition + '(' + @fieldPosX + '<' + cast(@maxX as varchar(50)) + ')'
set @sqlCondition = @sqlCondition + ' and '
set @sqlCondition = @sqlCondition + '(' + @fieldPosX + '>' + cast(@minX as varchar(50)) + ')'
set @sqlCondition = @sqlCondition + ' and '
set @sqlCondition = @sqlCondition + '(' + @fieldPosY + '<' + cast(@maxY as varchar(50)) + ')'
set @sqlCondition = @sqlCondition + ' and '
set @sqlCondition = @sqlCondition + '(' + @fieldPosY + '>' + cast(@minY as varchar(50)) + ')'
end
else
begin
return (1)
end
end
else
begin
-- 否則按照方位來查
-- 從方位配置表中搜索相應的方位所在的位置和該方位的大小
declare @minFlagX float
declare @minFlagY float
declare @maxFlagX float
declare @maxFlagY float
declare @readFlagRangeRtn int
exec @readFlagRangeRtn=usersp_chwl_readFlagRange @directPosValue, @minFlagX output , @maxFlagX output , @minFlagY output , @maxFlagY output
if (0 <> @readFlagRangeRtn)
begin
return (3)
end
if (@minFlagX is null or @minFlagY is null or @maxFlagX is null or @maxFlagY is null)
begin
return (4)
end
set @sqlCondition = @sqlCondition + '(' + @fieldPosX + '<' + cast(@maxFlagX as varchar(50)) + ')'
set @sqlCondition = @sqlCondition + ' and '
set @sqlCondition = @sqlCondition + '(' + @fieldPosX + '>' + cast(@minFlagX as varchar(50)) + ')'
set @sqlCondition = @sqlCondition + ' and '
set @sqlCondition = @sqlCondition + '(' + @fieldPosY + '<' + cast(@maxFlagY as varchar(50)) + ')'
set @sqlCondition = @sqlCondition + ' and '
set @sqlCondition = @sqlCondition + '(' + @fieldPosY + '>' + cast(@minFlagY as varchar(50)) + ')'
end
end
end
-- 備注
if (0 < len(@memoValue))
begin
if (0 < len(@sqlCondition)) set @sqlCondition = @sqlCondition + ' and '
set @sqlCondition = @sqlCondition + '(' + @fieldMemo + ' like ''%' + @memoValue + '%'')'
end
-- 讀取配置, 看選擇多少行數據
declare @Recordnumber varchar(200)
set @Recordnumber = ''
exec usersp_chwl_readCfgParamOfRecordNumber @Recordnumber output
-- 預做相關數據的轉換
declare @recordNum nvarchar(100)
set @recordNum = cast(@Recordnumber as nvarchar(100))
declare @nvQueryTab nvarchar(500)
set @nvQueryTab = cast(@queryTableName as nvarchar(500))
declare @nSqlQuery nvarchar(4000)
-- 開始查詢
create table #keytable(F_ID varchar(100)) -- 存儲包含關鍵字的結果表
declare @nNormQuery nvarchar(4000)
declare @nKeyQuery nvarchar(4000)
-- 有關鍵字
if (0 <len( @keyValue))
begin
declare @keyIDQuery nvarchar(300)
set @keyIDQuery = N' select F_POIID ' +
N' from T_POI_INFO_KEY' +
N' where '+@fieldKey+'='''+@keyValue + ''''
set @keyIDQuery = N' insert #keytable '+@keyIDQuery
exec sp_executesql @keyIDQuery
set @nKeyQuery = N'select ' +
N' ' + cast( @resultFields as nvarchar(500)) +
N' from ' + @nvQueryTab +
N' where ' + @fieldID + ' in (select * from #keytable)'
end
--包含其它查詢條件
if (0 < len(@sqlCondition))
begin
declare @KEYRecords int
select @KEYRecords=count(*) from #keytable
declare @normalCount int
set @normalCount = (cast(@recordNum as int) - @KEYRecords)
set @nNormQuery = N'select top ' +cast(@normalCount as nvarchar(100))+
N' ' + cast( @resultFields as nvarchar(500)) +
N' from ' + @nvQueryTab +
N' where ' + cast(@sqlCondition as nvarchar(2000))
if (0<@KEYRecords )
begin
set @nNormQuery =@nNormQuery+N' and (' + @fieldID + ' not in (select * from #keytable))'
end
set @nNormQuery =@nNormQuery+N' order by ' +@fieldKey+' desc ,'+@fieldName
if (0<@KEYRecords )
set @nSqlQuery = @nKeyQuery + N' union all ' + @nNormQuery
else
set @nSqlQuery = @nNormQuery
end
else
begin
-- 選擇0行數據
--set @nNormQuery = N'select top 0 ' + @resultFields + ' from ' + @nvQueryTab
set @nSqlQuery = @nKeyQuery
end
EXEC sp_executesql @nSqlQuery
drop table #keytable
return (0)
end
go?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -