函数如下:
CREATE FUNCTION oka(@one int)
returns varchar(8000)
as
begin
Declare @result1 Varchar(1000)
Declare @result2 Varchar(1000)
set @result1=''
set @result2=''select @result1=product_table.pr_fittings from product_table where product_table.pr_id=@one
if @result1<>''
select @result2=@result2+','+ fittings_table.fi_name+ fittings_table.fi_model+ cast( fittings_table.fi_price as varchar(1000) ) from .fittings_table where cast(.fittings_table. fi_id as varchar(1000) ) in (@result1)If len(@result2)>0
Set @result2=Right(@result2,len(@result2)-1)return @result2
end运行后没有报错,但无结果
如果改为返回 @result1 有结果,如果把@result1的结果直接写入 in (@result1) 里,有结果 请大家帮我看看
CREATE FUNCTION oka(@one int)
returns varchar(8000)
as
begin
Declare @result1 Varchar(1000)
Declare @result2 Varchar(1000)
set @result1=''
set @result2=''select @result1=product_table.pr_fittings from product_table where product_table.pr_id=@one
if @result1<>''
select @result2=@result2+','+ fittings_table.fi_name+ fittings_table.fi_model+ cast( fittings_table.fi_price as varchar(1000) ) from .fittings_table where cast(.fittings_table. fi_id as varchar(1000) ) in (@result1)If len(@result2)>0
Set @result2=Right(@result2,len(@result2)-1)return @result2
end运行后没有报错,但无结果
如果改为返回 @result1 有结果,如果把@result1的结果直接写入 in (@result1) 里,有结果 请大家帮我看看
解决方案 »
- 两个实体间联系是多对多,怎么转换成关系模式,并创建表?
- Help to add foreign key
- 这个SQL 检索怎么做?
- 能通俗的讲一下什么是簇索引,什么是非簇索引及这两者之间的区别吗?另问索引到底是什么,有什么用处?谢谢
- 请高手来帮忙解决一个作业+存储进城问题
- 关于数据库事件的问题
- 怎么优先查出某些记录?
- 如何根据字段内容里面包含的信息修改? 没有成功
- 存储过程的困惑---如何输入变量字段
- 求一个关于分组的SQL,非常感谢--------------------------------------------------------------
- 求一Sql语句。。。
- 急求一SQL文。谢谢!
如果 返回 @result1 带入参数1 结果为:14,16,28,41,42,43,44
如果把14,16,28,41,42,43,44直接写在in()里面,返回 @result2是有值的 但直接运行就无值 只显示 批查询完成
--try
CREATE FUNCTION oka(@one int)
returns varchar(8000)
as begin
Declare @result Varchar(1000) set @result='' select @result=@result+','+ fittings_table.fi_name+ fittings_table.fi_model+ cast( fittings_table.fi_price as varchar(1000) )
from fittings_table where cast(fittings_table. fi_id as varchar(1000) )
in (select product_table.pr_fittings from product_table where product_table.pr_id=@one) If @result <> ''
Set @result=Right(@result,len(@result)-1) return @result
end
建表1:
Create table products_table1
(
pr_ID1 Int not Null,
pr_fittings1 Varchar(100) Not Null,
)
Alter Table products_table1
Add Constraint PK_products_table1 PRIMARY KEY(pr_id1,pr_fittings1)insert into products_table1 values(1,'1,2,3')
insert into products_table1 values(2,'1,2,4')
insert into products_table1 values(3,'1,3,4')建表2:
Create table fitting_table1
(
fi_ID1 Int not Null,
fi_name1 Varchar(100) Not Null,
fi_model1 Varchar(100) Not Null,
fi_price1 money Not Null
)
Alter Table fitting_table1
Add Constraint PK_fitting_table1 PRIMARY KEY(fi_ID1,fi_name1,fi_model1,fi_price1)insert into fitting_table1 values(1,'天平','bs224s',$1000)
insert into fitting_table1 values(2,'天平2','ba124s',$1200)
insert into fitting_table1 values(3,'天平3','bsa124s',$1300)
insert into fitting_table1 values(3,'天平4','cpa124s',$1400)
函数:CREATE FUNCTION oka1(@one int)
returns varchar(8000)
as begin
Declare @result Varchar(1000) set @result='' select @result=@result+','+ fitting_table1.fi_name1+ fitting_table1.fi_model1+ cast( fitting_table1.fi_price1 as varchar(1000) )
from fitting_table1 where cast(fitting_table1. fi_id1 as varchar(1000) )
in (select pr_fittings1 from products_table1 where pr_id1=@one) If @result <> ''
Set @result=Right(@result,len(@result)-1) return @result
end
运用 print dbo.oka(1) 无结果
但把1,2,3 带入就有结果
不解 请大家帮忙看看原表太大 实在不方便
select pr_fittings1 from products_table1 where pr_id1=1
--结果
1,2,3
select @result=@result+','+ fitting_table1.fi_name1+ fitting_table1.fi_model1+ cast( fitting_table1.fi_price1 as varchar(1000) )
from fitting_table1 where cast(fitting_table1. fi_id1 as varchar(1000) )
in ('1,2,3')
--这个是字符串,你还得处理下
中间那句查询改成这样看看select @result=@result+','+ a.fi_name1+ a.fi_model1+ cast( a.fi_price1 as varchar(1000) )
from fitting_table1 a, products_table1 b
where b.pr_id1=@one and charindex(cast(a.fi_id1 as varchar(1000) ) ,pr_fittings1)>0
这句不满足条件,导制@result2无数据吧.楼主要把这句改成WHERE charindex(cast(.fittings_table. fi_id as varchar(1000) )+',',@result1+',')>0应可以的.
强大! ok! 再研究下你的代码
非常感谢 icelovey 和 nianran520
--改成这样,你那样写不行
/*=============================================*/
--> author:Ken Wong
--> Add date:2009-12-05 11:21:11
/*=============================================*/
CREATE FUNCTION oka(@one int)
returns varchar(8000)
as begin
Declare @result Varchar(1000)
declare @str varchar(1000)
declare @pos int
declare @ret varchar(20)declare @table table(ret varchar(20))select @str = product_table.pr_fittings from product_table where product_table.pr_id=@one
select @str = @str + ','
select @pos = charindex(',',@str)while @pos > 0
begin
select @ret = substring(@str,1,@pos-1)
insert into @table select @ret
select @str = stuff(@str,1,@pos,'')
select @pos = charindex(',',@str)
end set @result='' select @result=@result+','+ fittings_table.fi_name+ fittings_table.fi_model+ cast( fittings_table.fi_price as varchar(1000) )
from fittings_table where cast(fittings_table. fi_id as varchar(1000) )
in (select ret from @table) return stuff(@result,1,1,'')end