大力帮忙，存储过程问题，有TEXT字段类型的记录不能UNION

[huayuxing]

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE dh_GetQueryResult (@TableName varchar(100),@QueryString varchar(100),@StrCondition varchar(100))

AS declare @Ssql varchar(8000) DECLARE Query_cursor CURSOR FOR

Select S.Name from SysColumns S inner join SysObjects O on S.ID=O.ID Where O.Name=@TableName

declare @FName varchar(100)

open Query_cursor

fetch Query_cursor into @FName

Set @Ssql='(Select * from '+@TableName +' Where Cast ('+@FName +' as varchar) like ''%' +@QueryString +'%'''+@Strcondition+')'

fetch next from Query_cursor into @FName

while @@fetch_status=0

begin

set @Ssql=@Ssql+( ' union (Select * from '+@TableName +' Where Cast ('+@FName +' as varchar) like ''%' +@QueryString+'%'''+@Strcondition+')')

fetch next from Query_cursor into @FName

end

close Query_cursor

deallocate Query_cursor

Exec(@Ssql)

return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


其中有几个字段是TEXT类型的，任一查询都没问题，UNION了以后就说DISTINCT不能用于TEXT，IMAGE字段，
应该是对UNION的记录集自动DISTINCT了，急，谢谢

[zjcxc]

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROCEDURE dh_GetQueryResult (@TableName varchar(100),@QueryString varchar(100),@StrCondition varchar(100))

AS declare @Ssql varchar(8000) DECLARE Query_cursor CURSOR FOR

Select S.Name from SysColumns S inner join SysObjects O on S.ID=O.ID Where O.Name=@TableName

declare @FName varchar(100)

open Query_cursor

fetch Query_cursor into @FName

Set @Ssql='(Select * from '+@TableName +' Where Cast ('+@FName +' as varchar) like ''%' +@QueryString +'%'''+@Strcondition+')'

fetch next from Query_cursor into @FName

while @@fetch_status=0

begin

set @Ssql=@Ssql+( ' union all (Select * from '+@TableName +' Where Cast ('+@FName +' as varchar) like ''%' +@QueryString+'%'''+@Strcondition+')')

fetch next from Query_cursor into @FName

end

close Query_cursor

deallocate Query_cursor

Exec(@Ssql)

return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

[zjcxc]

因为union会去掉重复记录(相当于distinct),而text字段是不支持的,所以你改用:
union all 就行了.

[huayuxing]

union all 会所有的记录都重复显示出来的，

[linxu]

CREATE PROCEDURE dh_GetQueryResult (@TableName varchar(100),@QueryString varchar(100),@StrCondition varchar(100))

AS declare @Ssql varchar(8000) DECLARE Query_cursor CURSOR FOR

Select S.Name from SysColumns S inner join SysObjects O on S.ID=O.ID Where O.Name=@TableName

declare @FName varchar(100)

open Query_cursor

fetch Query_cursor into @FName

Set @Ssql='Select * from '+@TableName +' Where (Cast ('+@FName +' as varchar) like ''%' +@QueryString +'%'''

fetch next from Query_cursor into @FName

while @@fetch_status=0

begin

set @Ssql=@Ssql+ ' or Cast ('+@FName +' as varchar) like ''%' +@QueryString +'%'''

fetch next from Query_cursor into @FName

end
set @Ssql=@Ssql+')'+@Strcondition

close Query_cursor

deallocate Query_cursor

Exec(@Ssql)

return