sql中没有对text类型变量的操作方法的,所以只能变通
解决方案 »
- 求助,VC中如何将SQL Server的表导出为Excel表格
- 如何删除SQL表中重复的记录,除ID值不一样外其它字段都一样
- 一个语句的条件怎么写?
- 【急急急】为什么我将一字段为Ntext类型的数据转换为nvarchar时会发生如此奇怪现象??
- Sql Server Express 无法从另一台外网机子远程连接
- 问一条sql语句?(会者不难!)
- 请问:我用vb写了一个dll, 用asp调用,往sqlserver2000中插入数据,大概有10万条....
- 请问 如何把已有的SQL SERVSER 2000 的数据库导入其中
- 这句SQL对不对?
- 出现Could not open FCB for invalid file 问题
- 长度问题
- 数据库字段为decimal,读取出来的值是.3而不是0.3,但60.55就是60.55,这个问题该怎么解决?
as
declare @strXML varchar
declare
@daterq datetime,
@chvdjh varchar(50),
@chvzy varchar(150),
@intjf_sl_f decimal(18,2),
@intjf_lsj decimal(18,2),
@intdf_sl_f decimal(18,2),
@intdf_lsj decimal(18,2),
@intye_sl_f decimal(18,2),
@intye_lsj decimal(18,2),
@intnd int,
@intyf int declare @Yfmxz cursor Set @Yfmxz=cursor for
Select top 10 rq,djh,zy,jf_sl_f,jf_lsj,df_sl_f,df_lsj,ye_sl_f,ye_lsj,nd,yf from yfmxz where ypbm='17032' and yfbh='10' and nd=2004 and yf=4 order by id
Open @Yfmxz
Fetch Next From @Yfmxz
Into @daterq,@chvdjh,@chvzy,@intjf_sl_f,@intjf_lsj,@intdf_sl_f,@intdf_lsj,@intye_sl_f,@intye_lsj,@intnd,@intyf
--Set @strXML =convert(text(2100000),@strXML)
Set @strXML ='<?xml version=1.0?><EtCell><ROWDATA>' While (@@FETCH_STATUS=0)
Begin
Set @strXML=@strXML + '<ROW><rq>' + convert(varchar(10),@daterq) + '</rq><djh>' + convert(varchar(15),@chvdjh )+ '</djh><zy>' + convert(varchar(150),@chvzy) + '</zy><je_sl_f>' + convert(varchar(20),@intjf_sl_f )+ '</jf_sl_f><jf_lsj>' + convert(varchar(20),@intjf_lsj)+ '</jf_lsj><df_sl_f>' + convert(varchar(20),@intdf_sl_f) + '</df_sl_f><df_lsj>' + convert(varchar(20),@intdf_lsj) + '</df_lsj><ye_sl_f>' +convert(varchar(20), @intye_sl_f) + '</ye_sl_f><ye_lsj>' +convert(varchar(20), @intye_lsj) + '</ye_lsj><nd>' +convert(varchar(20),@intnd )+ '</nd><yf>' + convert(varchar(20),@intyf) + '</yf></ROW>'
Fetch Next From @Yfmxz
Into @daterq,@chvdjh,@chvzy,@intjf_sl_f,@intjf_lsj,@intdf_sl_f,@intdf_lsj,@intye_sl_f,@intye_lsj,@intnd,@intyf
End
Set @strXML=@strXML + '</ROWDATA></EtCell>'
print @strXML
Close @Yfmxz
Deallocate @Yfmxz
GO大家帮忙看一下有什么问题!!!
^^^^^^^^^^^^^^^^^^^^^^^^^^这个定义太晕了吧? 这样定义的话 @strXML 的长度为1改为:
declare @strXML varchar(8000)
as
declare @strXML1 varchar(8000),@strXML2 varchar(8000)
select @strXML1='',@strXML2=''
Select top 10 id=identity(int,0,1),re
= '<ROW><rq>' + convert(varchar(10),rq)
+ '</rq><djh>' + convert(varchar(15),djh )
+ '</djh><zy>' + convert(varchar(150),zy)
+ '</zy><je_sl_f>' + convert(varchar(20),jf_sl_f )
+ '</jf_sl_f><jf_lsj>' + convert(varchar(20),jf_lsj)
+ '</jf_lsj><df_sl_f>' + convert(varchar(20),df_sl_f)
+ '</df_sl_f><df_lsj>' + convert(varchar(20),df_lsj)
+ '</df_lsj><ye_sl_f>' +convert(varchar(20), ye_sl_f)
+ '</ye_sl_f><ye_lsj>' +convert(varchar(20), ye_lsj)
+ '</ye_lsj><nd>' +convert(varchar(20),nd )
+ '</nd><yf>' + convert(varchar(20),yf)
+ '</yf></ROW>'
into #t from yfmxz
where ypbm='17032' and yfbh='10' and nd=2004 and yf=4
order by id select @strXML1=@strXML1+re
from #t where id<5 select @strXML2=@strXML2+re
from #t where id>=5 select @strXML1='<?xml version=1.0?><EtCell><ROWDATA>'+@strXML1
,@strXML2=@strXML2+'</ROWDATA></EtCell>' print @strXML1+@strXML2GO