你传的值是否是varchar(8000)呢?
解决方案 »
- 数据库视图SA用户查询没数据,另一个用户查询却正常?!
- 使用Create创建学生信息表时,怎样限定学生性别只能为男或者女
- 求sql语句,空值和数字混合排序
- 分组查询问题?
- 请教从哪里可以下载sql server2000?
- 知道的朋友来帮个忙!!sql
- 这样的表连接怎么写sql语句?
- 删除远程表报错
- WIN2K 的问题:1、怎么允许本地普通用户(user)关机;2、怎么能让本地普通用户开机后,客户机连接到SQL SERVER?3、为什么我的SQL量SERVER2K需要停止然后再启动客户才能用?
- 在线请教,多表查询sql怎么写
- 数据筛选!疑感…………
- 关于sql中循环语句的查询,想了一整天也没想出方法
传入的参数:
CREATE PROCEDURE [dbo].[pr_OutSuccessData]
@feebank varchar(10) ,
@unitcode varchar(4) ,
@feecode varchar(3) ,
@cuu varchar(2),
@accnotype varchar(2),
@cdflag varchar(1),
@filename varchar(50),
@ofile_name varchar(50),
@ofile_user varchar(10),
@ofile_bank varchar(7),
@paydate1 varchar(10),
@paydate2 varchar(10),
@gyout varchar(1) declare @insertStr varchar(8000)set @insertStr = ' insert into ##outsuccesstmp(username,accno,amt,pay_date,register_User,re)'
select @insertStr = @insertStr+ ' select fee.username,fee.accno,convert(varchar(20),fee.amt,1) ,convert(varchar(20),fee.pay_date,111) ,fee.register_user,feecodeinfo.re from fee,feecodeinfo'
select @insertStr = @insertStr+ ' where fee.feebank='''+@feebank +'''' + ' and fee.unitcode=''' + @unitcode +' and fee.cuu='''+ @cuu +''''+' and fee.accnotype='''+@accnotype +' and fee.cdflag='''+@cdflag+''''+' and fee.pay_flag=''是'' '
select @insertStr = @insertStr+ ' and fee.pay_date >= '''+@paydate1+''''+' and fee.pay_date <= '''+@paydate2 +''''+' and fee.unitcode = feecodeinfo.unitcode and fee.feecode = feecodeinfo.feecode '
select @insertStr = @insertStr+ ' order by fee.pay_date,fee.username'select '长度'=len(@insertStr)
select @insertStr
a:
你定义了一个 varchar(8000)的变量@s, 赋个很长的字串给它(可能超过1000或2000,但不至于不到600), 然后用 select len(@s) 显示500
b:
你定义了一个 varchar(8000)的变量@s,赋了个字串给它,然后用 select len(@s) 显示500
如果是a, 那么显然某个地方有误.
以你在上面贴的代码来看, 对@insert操作的语句有 5 行,每行 80-200字符. 第一行,没有其它变量参数的参数操作.那么第一行得到80左右的字符.
如果二,三,四,五行,任有一个要+的变量为null,那么结果为null,导至最终结果亦为 null.
这种推断与 a 的情况描述不符.如果b,5行加起来也差不多500多字符.
难道是你认为:
declare @s varchar(1000)
set @s='a'
然后 len(@s) 就应该为 1000?
如果是这种想法那就大错特错了,当然,我想你也不会有这种想法.所以我猜不出你想表达什么. 或者说,你在楼上的代码里,你认为select len(@insertSTR)的结果应该是多少?
我的思路如楼上所说的a情况相类似。目的是五行字符串相加,得到一个字符串。但实际上的情况是五行字符串相加后,得到的@insertStr只是五行字符串相加后的一部分字符串,且取长度为500多字符,根本没超过定义的8000长度!
CREATE PROCEDURE [dbo].[pr_Test]
@feebank varchar(10) ,
@unitcode varchar(4) ,
@feecode varchar(3) ,
@cuu varchar(2),
@accnotype varchar(2),
@cdflag varchar(1),
@filename varchar(50),
@ofile_name varchar(50),
@ofile_user varchar(10),
@ofile_bank varchar(7),
@paydate1 varchar(10),
@paydate2 varchar(10),
@gyout varchar(1)
AS
declare @insertStr varchar(8000)set @insertStr = ' insert into ##outsuccesstmp(username,accno,amt,pay_date,register_User,re)'
set @insertStr = @insertStr + ' select fee.username,fee.accno,convert(varchar(20),fee.amt,1) ,convert(varchar(20),fee.pay_date,111) ,fee.register_user,feecodeinfo.re from fee,feecodeinfo'
set @insertStr = @insertStr + ' where fee.feebank='''+@feebank +'''' + ' and fee.unitcode=''' + @unitcode +' and fee.cuu='''+ @cuu +''''+' and fee.accnotype='''+@accnotype +' and fee.cdflag='''+@cdflag+''''+' and fee.pay_flag=''是'' '
set @insertStr = @insertStr + ' and fee.pay_date >= '''+@paydate1+''''+' and fee.pay_date <= '''+@paydate2 +''''+' and fee.unitcode = feecodeinfo.unitcode and fee.feecode = feecodeinfo.feecode '
set @insertStr = @insertStr + ' order by fee.pay_date,fee.username'select '长度'=len(@insertStr)
select '字符串值'=@insertStr
GO
在查询分析器中执行的Sql语句
exec dbo.pr_test
'4750208','J185','134','01','00','D','d:/tmp123','tmp123.txt','1001','4750208','2007-07-23','2007-07-27','1'得到的结果:
长度:554
字符串值=' insert into ##outsuccesstmp(username,accno,amt,pay_date,register_User,re) select fee.username,fee.accno,convert(varchar(20),fee.amt,1) ,convert(varchar(20),fee.pay_date,111) ,fee.register_user,feecodeinfo.re from fee,feecodeinfo where fee.feebank'希望大家试一下,看得到的结果是不是一致的,谢谢了!
@feebank varchar(10) ,
@unitcode varchar(4) ,
@feecode varchar(3) ,
@cuu varchar(2),
@accnotype varchar(2),
@cdflag varchar(1),
@filename varchar(50),
@ofile_name varchar(50),
@ofile_user varchar(10),
@ofile_bank varchar(7),
@paydate1 varchar(10),
@paydate2 varchar(10),
@gyout varchar(1)
AS
declare @insertStr varchar(8000) set @insertStr = ' insert into ##outsuccesstmp(username,accno,amt,pay_date,register_User,re)'
set @insertStr = @insertStr +
' select
fee.username,fee.accno,convert(varchar(20),fee.amt,1) ,
convert(varchar(20),fee.pay_date,111)
,fee.register_user,feecodeinfo.re
from fee,feecodeinfo'
set @insertStr = @insertStr +
' where fee.feebank='''+@feebank +''' and fee.unitcode=''' + @unitcode
+''' and fee.cuu='''+ @cuu +''' and fee.accnotype='''+@accnotype
+''' and fee.cdflag='''+@cdflag+''' and fee.pay_flag=''是'' '
set @insertStr = @insertStr + ' and fee.pay_date > = '''+@paydate1+''''+' and fee.pay_date <= '''+@paydate2 +''' and fee.unitcode = feecodeinfo.unitcode and fee.feecode = feecodeinfo.feecode '
set @insertStr = @insertStr + ' order by fee.pay_date,fee.username' select '长度'=len(@insertStr)
select '字符串值'=@insertStr
GO
应该楼主用了select @insertStr 的原因,换成print @insertStr ,你就会看到正确结果了或者查询分析器工具->选项->结果,每列最多字符数改为4000
长度为:651
字符串值:" insert into ##outsuccesstmp(username,accno,amt,pay_date,register_User,re) select fee.username,fee.accno,convert(varchar(20),fee.amt,1) , convert(varchar(20),fee.pay_date,111) ,fee.register_user,feecodeinfo.re from fee,feec"请注意:该字符串只取了上面五个字符串相加后的前一段!
insert into ##outsuccesstmp(username,accno,amt,pay_date,register_User,re) select fee.username,fee.accno,convert(varchar(20),fee.amt,1) ,convert(varchar(20),fee.pay_date,111) ,fee.register_user,feecodeinfo.re from fee,feecodeinfo where fee.feebank='4750208' and fee.unitcode='J185 and fee.cuu='01' and fee.accnotype='00 and fee.cdflag='D' and fee.pay_flag='是' and fee.pay_date > = '2007-07-23' and fee.pay_date <= '2007-07-27' and fee.unitcode = feecodeinfo.unitcode and fee.feecode = feecodeinfo.feecode order by fee.pay_date,fee.usernamefee.unitcode,fee.accnotype后面都少了个引号