📄 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 + -