-- 修正一个小BUB----------------------------------------------------------------
CREATE PROCEDURE SP_AdvanceSearch
(@TableName Nvarchar(200) --表名
,@ViewColumns Nvarchar(200)='*' --需要查询的字段列表
,@SearchColumnName Nvarchar(100) --被搜索的字段名
,@KeyWord Nvarchar(200) --搜索关键字
)
AS
---开始处理关键字
---**********************
declare @TempKeyWord Nvarchar(200)
,@CurrKeyWord Nvarchar(200)declare @KH_Q Nvarchar(20),@KH_H Nvarchar(20)
declare @KeyCount int,@I int,@J int,@FirstWordLocal int
declare @AndOr varchar(20),@WhereStr Nvarchar(2000)if @KeyWord<>N''
begin
set @KeyWord=Lower(@KeyWord) set @KeyWord=replace(@KeyWord,')',')') --处理全角的括号
set @KeyWord=replace(@KeyWord,'(','(')
set @KeyWord=replace(@KeyWord,' ',' ')
set @KeyWord=replace(@KeyWord,'+','+') --处理全角+-|
set @KeyWord=replace(@KeyWord,'-','-')
set @KeyWord=replace(@KeyWord,'|','|') set @KeyWord=replace(@KeyWord,' and ','+') --处理全角英文关键字
set @KeyWord=replace(@KeyWord,' or ','|')
set @KeyWord=replace(@KeyWord,' not ','-')
if @KeyWord=replace(replace(replace(replace(@KeyWord,'-',''),'|',''),'(',''),')','')
set @KeyWord=replace(@KeyWord,' ','+')
else
set @KeyWord=replace(@KeyWord,' ','') set @KeyWord=replace(@KeyWord,'+',';+')
set @KeyWord=replace(@KeyWord,'-',';-')
set @KeyWord=replace(@KeyWord,'|',';|')
if len(replace(@KeyWord,'(',''))<>len(replace(@KeyWord,')',''))
set @KeyWord='' --前后括号不匹配 Set @WhereStr=' where '
if @KeyWord<>N''
begin
--生成搜索字符串
set @KeyWord=@KeyWord+';' set @KeyCount=len(@KeyWord)-len(replace(@KeyWord,';',''))
set @TempKeyWord=@KeyWord set @I=1 while (@I<=@KeyCount)
begin
set @FirstWordLocal=CharIndex(';',@TempKeyWord)
set @CurrKeyWord=rtrim(ltrim(left(@TempKeyWord,@FirstWordLocal-1)))
set @TempKeyWord=rtrim(ltrim(right(@TempKeyWord,len(@TempKeyWord)-@FirstWordLocal)))
if (@CurrKeyWord='' or replace(replace(replace(replace(replace(@CurrKeyWord,'+',''),'-',''),'|',''),'(',''),')','') ='')
begin
set @WhereStr=@WhereStr +replace(replace(replace(@CurrKeyWord,'+',''),'-',''),'|','')
set @I=@I+1
CONTINUE
end set @KH_Q=''
set @KH_H=''
--检查前面的括号
Set @j=1
while (substring(@CurrKeyWord,@j,1)='('
or substring(@CurrKeyWord,@j,1)='+'
or substring(@CurrKeyWord,@j,1)='-'
or substring(@CurrKeyWord,@j,1)='|')
begin if substring(@CurrKeyWord,@j,1)='('
set @KH_Q=@KH_Q + '(' set @j=@j+1
end --检查后面的括号
Set @j=len(@CurrKeyWord)
while (substring(@CurrKeyWord,@j,1)=')')
begin
set @KH_H=@KH_H + ')'
set @j=@j-1
end set @AndOr=''
if substring(@CurrKeyWord,1,1)='+'
set @AndOr='and' if substring(@CurrKeyWord,1,1)='-'
set @AndOr='and not' if substring(@CurrKeyWord,1,1)='|'
set @AndOr='or'
Set @CurrKeyWord=replace(@CurrKeyWord,'+','')
Set @CurrKeyWord=replace(@CurrKeyWord,'-','')
Set @CurrKeyWord=replace(@CurrKeyWord,'|','')
Set @CurrKeyWord=replace(@CurrKeyWord,')','')
Set @CurrKeyWord=replace(@CurrKeyWord,'(','') Set @WhereStr=@WhereStr + ' ' + @AndOr + ' ' + @KH_Q + ' ' + @SearchColumnName +' like ''%' + @CurrKeyWord + '%'' ' + @KH_H set @I=@I+1
end
end
end
exec ( 'select ' +@ViewColumns + ' from ' +@TableName + @WhereStr)
GO
CREATE PROCEDURE SP_AdvanceSearch
(@TableName Nvarchar(200) --表名
,@ViewColumns Nvarchar(200)='*' --需要查询的字段列表
,@SearchColumnName Nvarchar(100) --被搜索的字段名
,@KeyWord Nvarchar(200) --搜索关键字
)
AS
---开始处理关键字
---**********************
declare @TempKeyWord Nvarchar(200)
,@CurrKeyWord Nvarchar(200)declare @KH_Q Nvarchar(20),@KH_H Nvarchar(20)
declare @KeyCount int,@I int,@J int,@FirstWordLocal int
declare @AndOr varchar(20),@WhereStr Nvarchar(2000)if @KeyWord<>N''
begin
set @KeyWord=Lower(@KeyWord) set @KeyWord=replace(@KeyWord,')',')') --处理全角的括号
set @KeyWord=replace(@KeyWord,'(','(')
set @KeyWord=replace(@KeyWord,' ',' ')
set @KeyWord=replace(@KeyWord,'+','+') --处理全角+-|
set @KeyWord=replace(@KeyWord,'-','-')
set @KeyWord=replace(@KeyWord,'|','|') set @KeyWord=replace(@KeyWord,' and ','+') --处理全角英文关键字
set @KeyWord=replace(@KeyWord,' or ','|')
set @KeyWord=replace(@KeyWord,' not ','-')
if @KeyWord=replace(replace(replace(replace(@KeyWord,'-',''),'|',''),'(',''),')','')
set @KeyWord=replace(@KeyWord,' ','+')
else
set @KeyWord=replace(@KeyWord,' ','') set @KeyWord=replace(@KeyWord,'+',';+')
set @KeyWord=replace(@KeyWord,'-',';-')
set @KeyWord=replace(@KeyWord,'|',';|')
if len(replace(@KeyWord,'(',''))<>len(replace(@KeyWord,')',''))
set @KeyWord='' --前后括号不匹配 Set @WhereStr=' where '
if @KeyWord<>N''
begin
--生成搜索字符串
set @KeyWord=@KeyWord+';' set @KeyCount=len(@KeyWord)-len(replace(@KeyWord,';',''))
set @TempKeyWord=@KeyWord set @I=1 while (@I<=@KeyCount)
begin
set @FirstWordLocal=CharIndex(';',@TempKeyWord)
set @CurrKeyWord=rtrim(ltrim(left(@TempKeyWord,@FirstWordLocal-1)))
set @TempKeyWord=rtrim(ltrim(right(@TempKeyWord,len(@TempKeyWord)-@FirstWordLocal)))
if (@CurrKeyWord='' or replace(replace(replace(replace(replace(@CurrKeyWord,'+',''),'-',''),'|',''),'(',''),')','') ='')
begin
set @WhereStr=@WhereStr +replace(replace(replace(@CurrKeyWord,'+',''),'-',''),'|','')
set @I=@I+1
CONTINUE
end set @KH_Q=''
set @KH_H=''
--检查前面的括号
Set @j=1
while (substring(@CurrKeyWord,@j,1)='('
or substring(@CurrKeyWord,@j,1)='+'
or substring(@CurrKeyWord,@j,1)='-'
or substring(@CurrKeyWord,@j,1)='|')
begin if substring(@CurrKeyWord,@j,1)='('
set @KH_Q=@KH_Q + '(' set @j=@j+1
end --检查后面的括号
Set @j=len(@CurrKeyWord)
while (substring(@CurrKeyWord,@j,1)=')')
begin
set @KH_H=@KH_H + ')'
set @j=@j-1
end set @AndOr=''
if substring(@CurrKeyWord,1,1)='+'
set @AndOr='and' if substring(@CurrKeyWord,1,1)='-'
set @AndOr='and not' if substring(@CurrKeyWord,1,1)='|'
set @AndOr='or'
Set @CurrKeyWord=replace(@CurrKeyWord,'+','')
Set @CurrKeyWord=replace(@CurrKeyWord,'-','')
Set @CurrKeyWord=replace(@CurrKeyWord,'|','')
Set @CurrKeyWord=replace(@CurrKeyWord,')','')
Set @CurrKeyWord=replace(@CurrKeyWord,'(','') Set @WhereStr=@WhereStr + ' ' + @AndOr + ' ' + @KH_Q + ' ' + @SearchColumnName +' like ''%' + @CurrKeyWord + '%'' ' + @KH_H set @I=@I+1
end
end
end
exec ( 'select ' +@ViewColumns + ' from ' +@TableName + @WhereStr)
GO
解决方案 »
- 请问sql server 2005 查询分析器能容错吗?
- SQLServer I/O写入次数很高,CPU100%
- 在线等答案 超基础的SQL2000问题
- 连接服务器服务器失败(1433端口无法开启)!
- 请教一个简单的存储过程,为什么只能更新数字不能更新字符?
- 请问视图和索引分别是做什么用的??
- 据说SQL Server2000的每个实例只能用到1.75G的内存, 有办法提高吗?
- 问题
- 请问如何设置,能让公网用户访问内网mssql数据库? 百分求教ing
- SQL语法中用什么函数把浮点型数值规范为带指定小数位的数?
- 如何将本地数据库中特定数据倒入到远程(走广域网)数据库服务器上
- 存储过程执行的阻塞问题,急!
补充一下对@TABLENAME判断:
....
eclare @TempKeyWord Nvarchar(200)
,@CurrKeyWord Nvarchar(200)declare @KH_Q Nvarchar(20),@KH_H Nvarchar(20)
declare @KeyCount int,@I int,@J int,@FirstWordLocal int
declare @AndOr varchar(20),@WhereStr Nvarchar(2000)If @TableName is null
begin
Print 'Please input table name!'
return
endif object_id(@TableName) is null
begin
Print 'The tablename input error,please check table name!'
return
endif @KeyWord<>N''
begin ....
---CREATE PROCEDURE SP_AdvanceSearch --author: CrazyFor
(@TableName Nvarchar(200) --表名
,@ViewColumns Nvarchar(200)='*' --需要查询的字段列表
,@SearchColumnName Nvarchar(100) --被搜索的字段名
,@KeyWord Nvarchar(200) --搜索关键字
)
AS
set nocount ondeclare @TempKeyWord Nvarchar(200)
,@CurrKeyWord Nvarchar(200)declare @KH_Q Nvarchar(20),@KH_H Nvarchar(20)
declare @KeyCount int,@I int,@J int,@FirstWordLocal int
declare @AndOr varchar(20),@WhereStr Nvarchar(2000)declare @CheckColumns Nvarchar(200)
----有效性检查
if @ViewColumns='*'
set @CheckColumns=@SearchColumnName
else
if charindex(',' +@SearchColumnName +',' ,',' + @ViewColumns + ',')>0
set @CheckColumns=@ViewColumns
else
set @CheckColumns=@ViewColumns + ',' + @SearchColumnNameif len(@CheckColumns)-len(replace(@CheckColumns,',',''))+1 <>
(select count(*) from
(select @CheckColumns as tmpCol) a
right join (select * from syscolumns ) b
on charindex(',' + b.[name] +',' , ',' + a.tmpCol +',') > 0
where b.[id]=object_id(@TableName) and a.tmpCol is not null)
begin
raiserror ('Error table name or column list!' , 16, -1)
return 0
end-----开始处理关键字
---**********************if @KeyWord<>N''
begin
set @KeyWord=Lower(@KeyWord) set @KeyWord=replace(@KeyWord,')',')') --处理全角的括号
set @KeyWord=replace(@KeyWord,'(','(')
set @KeyWord=replace(@KeyWord,' ',' ')
set @KeyWord=replace(@KeyWord,'+','+') --处理全角+-|
set @KeyWord=replace(@KeyWord,'-','-')
set @KeyWord=replace(@KeyWord,'|','|') set @KeyWord=replace(@KeyWord,' and ','+') --处理全角英文关键字
set @KeyWord=replace(@KeyWord,' or ','|')
set @KeyWord=replace(@KeyWord,' not ','-')
if @KeyWord=replace(replace(replace(replace(@KeyWord,'-',''),'|',''),'(',''),')','')
set @KeyWord=replace(@KeyWord,' ','+')
else
set @KeyWord=replace(@KeyWord,' ','') set @KeyWord=replace(@KeyWord,'+',';+')
set @KeyWord=replace(@KeyWord,'-',';-')
set @KeyWord=replace(@KeyWord,'|',';|')
if len(replace(@KeyWord,'(',''))<>len(replace(@KeyWord,')',''))
set @KeyWord='' --前后括号不匹配 Set @WhereStr=' where '
if @KeyWord<>N''
begin
--生成搜索字符串
set @KeyWord=@KeyWord+';' set @KeyCount=len(@KeyWord)-len(replace(@KeyWord,';',''))
set @TempKeyWord=@KeyWord set @I=1 while (@I<=@KeyCount)
begin
set @FirstWordLocal=CharIndex(';',@TempKeyWord)
set @CurrKeyWord=rtrim(ltrim(left(@TempKeyWord,@FirstWordLocal-1)))
set @TempKeyWord=rtrim(ltrim(right(@TempKeyWord,len(@TempKeyWord)-@FirstWordLocal)))
if (@CurrKeyWord='' or replace(replace(replace(replace(replace(@CurrKeyWord,'+',''),'-',''),'|',''),'(',''),')','') ='')
begin
set @WhereStr=@WhereStr +replace(replace(replace(@CurrKeyWord,'+',''),'-',''),'|','')
set @I=@I+1
CONTINUE
end set @KH_Q=''
set @KH_H=''
--检查前面的括号
Set @j=1
while (substring(@CurrKeyWord,@j,1)='('
or substring(@CurrKeyWord,@j,1)='+'
or substring(@CurrKeyWord,@j,1)='-'
or substring(@CurrKeyWord,@j,1)='|')
begin if substring(@CurrKeyWord,@j,1)='('
set @KH_Q=@KH_Q + '(' set @j=@j+1
end --检查后面的括号
Set @j=len(@CurrKeyWord)
while (substring(@CurrKeyWord,@j,1)=')')
begin
set @KH_H=@KH_H + ')'
set @j=@j-1
end set @AndOr=''
if substring(@CurrKeyWord,1,1)='+'
set @AndOr='and' if substring(@CurrKeyWord,1,1)='-'
set @AndOr='and not' if substring(@CurrKeyWord,1,1)='|'
set @AndOr='or'
Set @CurrKeyWord=replace(@CurrKeyWord,'+','')
Set @CurrKeyWord=replace(@CurrKeyWord,'-','')
Set @CurrKeyWord=replace(@CurrKeyWord,'|','')
Set @CurrKeyWord=replace(@CurrKeyWord,')','')
Set @CurrKeyWord=replace(@CurrKeyWord,'(','') Set @WhereStr=@WhereStr + ' ' + @AndOr + ' ' + @KH_Q + ' ' + @SearchColumnName +' like ''%' + @CurrKeyWord + '%'' ' + @KH_H set @I=@I+1
end
end
end
exec ( 'select ' +@ViewColumns + ' from ' +@TableName + @WhereStr)
GO