62,046
社区成员
发帖
与我相关
我的任务
分享
create fulltext index on TestTable
(
[name],
[Description]
Language 3076 -- 中文语言索引ID
)
create procedure TestFullTextSearch
(
@testText1 nvarchar(200),
@testText2 nvarchar(200)
-- 标准分页参数
@pageNumber int,
@rowEachPage int,
@minCurrentPageRowId int,
@maxCurrentPageRowId int,
@sortColumn nvarchar(150),
@sortOrder bit
)
as
begin
--定义最终结果表
declare @ResultTable table
(
Id int,
[Name] nvarchar(1000),
[Description] nvarchar(1000)
)
insert into @ResultTable
select Id, [Name], [Description] from dbo.TestTable tt
where @testText1 is null or contains(tt.Name, dbo.GetFullTextSearchText(@testText1)) -- 全文索引查询
and @testText2 is null or contains(tt.Description, dbo.GetFullTextSearchText(@testText2))
--分页 排序
select @minCurrentPageRowId = (@pageNumber * @rowEachPage)+1,
@maxCurrentPageRowId = (@pageNumber + 1) * @rowEachPage
if @sortOrder = 0 --升序
select * from
(
select row_number() over (order by case @sortColumn when 'id' then id end
when 'name' then name end ) as RowId, * from @ResultTable
) as t
where RowId between @minCurrentPageRowId and @maxCurrentPageRowId
else --降序
select * from
(
select row_number() over (order by case @sortColumn when 'id' then id end desc
when 'name' then name end desc ) as RowId, * from @ResultTable
) as t
where RowId between @minCurrentPageRowId and @maxCurrentPageRowId
end
--建立测试环境
create table table1
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10)
)
insert into table1
select 'a','b','c'
union
select 'a1','b1','c1'
union
select 'a2','b2','c2'
select * from table1
--拼接sql语句
declare @temp table
(
[id] int IDENTITY(1,1),
[Column_name] varchar(10)
)
declare @tempId int,@colName varchar(10),@sql varchar(500)
set @sql = 'select * from table1 where '
insert into @temp (Column_name)
SELECT dbo.syscolumns.name AS Column_name
FROM dbo.syscolumns INNER JOIN
dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id
WHERE dbo.sysobjects.name='table1'and
(dbo.sysobjects.xtype = 'u') AND (NOT (dbo.sysobjects.name LIKE 'dtproperties'))
--select * from @temp
WHILE EXISTS(select [id] from @temp)
begin
SET ROWCOUNT 1
select @tempId = [id],@colName=[Column_name] from @temp
SET ROWCOUNT 0
delete from @temp where [id] = @tempId
set @sql = @sql+' '+@colName +' like ''%a%'' or'
end
set @sql = stuff(@sql,len(@sql)-1,2,'')
exec (@sql)