ASP
yourstr = replace(yourstr,",","")SQL:
set @str = replace(@str,'''''',''',''')or:
set @str = replace(@str,char(39)+char(39),char(39)+','+char(39))
yourstr = replace(yourstr,",","")SQL:
set @str = replace(@str,'''''',''',''')or:
set @str = replace(@str,char(39)+char(39),char(39)+','+char(39))
先将逗号变成一个用不到的字母,如:'a'.成为'222'a'3456'a'56777';出来后,再将'a'变成逗号.
Studying...
也许是我讲的不够清楚!!!!
我的问题是在sql函数里将其转换成逗号‘,’
试了:@nValue=replace(@nValue,'+',',')
可在sql里编译不通过function dbo.fn_GetBZC_aa(@iYear smallint,@sSchool varchar(8000))
returns numeric(9,5)
as
begin
declare @nValue numeric(9,5)if @sSchool='' or @sSchool is null
select @nValue=stdevp(tjxx.sg)
from jk_xstj_tjxx as tjxx inner join jk_xstj_xsjkk as xsjkk on tjxx.sfzh=xsjkk.sfzh
inner join jk_xstj_bjxx bjxx on tjxx.bjxx_id=bjxx.bjxx_id
where tjxx.tjnf=@iYear
else
--@nValue=replace(@nValue,'+',',')
select @nValue=stdevp(tjxx.sg)
from jk_xstj_tjxx as tjxx inner join jk_xstj_xsjkk as xsjkk on tjxx.sfzh=xsjkk.sfzh
inner join jk_xstj_bjxx bjxx on tjxx.bjxx_id=bjxx.bjxx_id
where tjxx.tjnf=@iYear and (bjxx.xxjbxx_id in (@sSchool))return(@nValue)
END
returns numeric(9,5)
as
begin
declare @nValue numeric(9,5)if @sSchool='' or @sSchool is null
select @nValue=stdevp(tjxx.sg)
from jk_xstj_tjxx as tjxx inner join jk_xstj_xsjkk as xsjkk on tjxx.sfzh=xsjkk.sfzh
inner join jk_xstj_bjxx bjxx on tjxx.bjxx_id=bjxx.bjxx_id
where tjxx.tjnf=@iYear
else
set @nValue=replace(@nValue,'+',',')
select @nValue=stdevp(tjxx.sg)
from jk_xstj_tjxx as tjxx inner join jk_xstj_xsjkk as xsjkk on tjxx.sfzh=xsjkk.sfzh
inner join jk_xstj_bjxx bjxx on tjxx.bjxx_id=bjxx.bjxx_id
where tjxx.tjnf=@iYear and (bjxx.xxjbxx_id in (@sSchool))return(@nValue)
END
以上这句是做什么用的??错误原因在于@sSchool是varchar型,不能只能使用in(@sSchool)的方式,sql编译器会把@sSchool强制转换成int型,当只有一个id时,是没有问题;如果出现123,456的形式就会出错。
必须通过动态sql语句来解决这个问题。
能详细解说解说???
@str = 'select * from a where id in(' + @sInput + ')'
exec(@str)不过这个问题使用动态sql可能也有问题,因为你不是返回结果集,而是要返回一个标量值。所以,我能想到的办法是使用sqlserver的字符函数,将传入的参数拆分,插入一个临时表,然后再进行表连接,得到结果值。如果其他人有关于动态sql取得返回值的办法,多多指教。