SQL函数要实现的功能具体入下:
我数据库的表中字段如下(area_info)
id area_id
1 6002,6008,5001
2 3002,6028,5101
3 2002,6018,5501
我想实现的是
当我从外部搜索后又值传过来
比如搜索的search_area_id=6002,5223,2009 这里面有6002,5223,2009 3个值
我想实现他和表(area_info)中的area_id来比较,如果里边任意一个值和area_id 返回 1 否则返回0
请问这个函数怎么写呀,我下了一下午都没写出来。。望高手门指点指点呀~~在线等待!!!
PS:这个函数用存储过程来调用,主要是实现搜索,这个是里边的一个条件
我数据库的表中字段如下(area_info)
id area_id
1 6002,6008,5001
2 3002,6028,5101
3 2002,6018,5501
我想实现的是
当我从外部搜索后又值传过来
比如搜索的search_area_id=6002,5223,2009 这里面有6002,5223,2009 3个值
我想实现他和表(area_info)中的area_id来比较,如果里边任意一个值和area_id 返回 1 否则返回0
请问这个函数怎么写呀,我下了一下午都没写出来。。望高手门指点指点呀~~在线等待!!!
PS:这个函数用存储过程来调用,主要是实现搜索,这个是里边的一个条件
id area_id
1 6002,6008,5001
2 3002,6028,5101
3 2002,6018,5501
-------------select * from tb where charindex(',值,', ','+area_id+',') > 0
set @str = '6002'select * ,返回 = 1 from tb where charindex(','+@str+',' , ','+area_id+',') > 0
union all
select * ,返回 = 0 from tb where charindex(','+@str+',' , ','+area_id+',') <= 0如果要函数,其函数体如上,具体的就不写了.下班.
insert into os select 1,'6002,6008,5001'
insert into os select 2 , '3002,6028,5101'
insert into os select 3, '2002,6018,5501'
declare @search_area_id varchar(100)
declare @sql varchar(8000)
set @search_area_id='6002,5223,2009'
set @sql='charindex('','+replace(@search_area_id,',',','','',''+area_id+'','')>0 or charindex('',')+','','',''+area_id+'','')>0'
set @sql='select 1 from os where '+@sql
exec(@sql)
insert ta select
1 ,'6002,6008,5001' union select
2 ,'3002,6028,5101' union select
3 ,'2002,6018,5501'
go
declare @search_area_id varchar(1000)
set @search_area_id='6002,5223,2009'
declare @s varchar(8000)set @search_area_id = 'select '''+replace ( @search_area_id,',',''' as ser union all select ''')+''''
set @s = 'if exists(select 1 from ta a,('+@search_area_id+ ') b where charindex(b.ser,a.area_id) > 0 ) print 1 else print 0'
exec( @s)
/*
1
*/set @search_area_id='60012,5223,2009'
set @search_area_id = 'select '''+replace ( @search_area_id,',',''' as ser union all select ''')+''''
set @s = 'if exists(select 1 from ta a,('+@search_area_id+ ') b where charindex(b.ser,a.area_id) > 0 ) print 1 else print 0'
exec( @s)
/*0*/drop table ta
--函数内无法调用动态SQL,改成存储过程吧。。create table os(id int,area_id varchar(50))
insert into os select 1,'6002,6008,5001'
insert into os select 2 , '3002,6028,5101'
insert into os select 3, '2002,6018,5501'
create proc dddd
@search_area_id varchar(100)
as
declare @sql varchar(8000)
set @sql='charindex('','+replace(@search_area_id,',',','','',''+area_id+'','')>0 or charindex('',')+','','',''+area_id+'','')>0'
set @sql='if exists(select 1 from os where '+@sql+') select 1 else select 0'
exec(@sql)exec dddd @search_area_id='60022,5223,2009' --结果:0
exec dddd @search_area_id='6002,5223,2009' --结果:1