这个动态语句是怎么回事,独立时好着,合并就被截断了!

[netcup]

declare @sql varchar(1000),@sqlstring varchar(1000),@sqllast varchar(2000)
set @sql='select a.rq,d.danwbh,d.dwmch,c.spbh,c.spmch,c.shpgg,c.shpchd,b.shl,c.dw,b.pihao,b.sxrq from jxdjhz a join ywmxk b on a.djbh=b.xgdjbh join spkfk c on b.spid=c.spid join mchk d on a.dwbh=d.dwbh'
set @sqlstring=' where a.rq>=convert(char(8),dateadd(month,-1,getdate()),120)+'26' and a.rq<=convert(char(8),dateadd(month,-1,getdate()),120)+'25' and c.shpchd like '%西安杨森制药有限公司%''
set @sqllast=@sql+@sqlstring

每个变量独立查看都没问题.
但是@sqllast的数值就被截断了
select @sqllast

------
'select a.rq,d.danwbh,d.dwmch,c.spbh,c.spmch,c.shpgg,c.shpchd,b.shl,c.dw,b.pihao,b.sxrq from jxdjhz a join ywmxk b on a.djbh=b.xgdjbh join spkfk c on b.spid=c.spid join mchk d on a.dwbh=d.dwbh where a.rq>=convert(char(8),dateadd(month,-1,getdate()),120)+'2'在这里被截断了!!
怎么回事呢?

[青锋-SS]

是不是长度不够,换成如下定义试试:
declare @sql varchar(8000),@sqlstring varchar(8000),@sqllast varchar(8000)

[dawugui]

declare @sql varchar(8000),@sqlstring varchar(8000),@sqllast varchar(8000)

[dawugui]

是不是长度不够

[青锋-SS]

楼主的语句有问题啊:
set @sqlstring=' where a.rq> =convert(char(8),dateadd(month,-1,getdate()),120)+'26' and a.rq <=convert(char(8),dateadd(month,-1,getdate()),120)+'25' and c.shpchd like ' %西安杨森制药有限公司%''

上面26和25是什么意思?

[netcup]

全部结构如下:

CREATE TABLE [dbo].[email_list] (
	[id] [int] IDENTITY (1, 1) NOT NULL ,
	[shpchd] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[email] [varchar] (36) COLLATE Chinese_PRC_CI_AS NULL ,
	[qsrq] [varchar] (60) COLLATE Chinese_PRC_CI_AS NULL ,
	[jsrq] [varchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
	[aday] [smallint] NULL ,
	[ahour] [int] NULL ,
	[enable] [bit] NULL 
) ON [PRIMARY]

insert email_list values('西安扬森制药有限公司','[e-mail]','convert(char(8),dateadd(month,-1,getdate()),120)+''01''','convert(char(10),dateadd(day,-1,convert(char(8),getdate(),120)+''01''),120)',1,12,1)

select @qsrq=qsrq,@jsrq=jsrq,@shpchd=shpchd from email_list where datepart(day,getdate())=aday and datepart(hour,getdate())=ahour

set @sql='select a.rq,d.danwbh,d.dwmch,c.spbh,c.spmch,c.shpgg,c.shpchd,b.shl,c.dw,b.pihao,b.sxrq from jxdjhz a join ywmxk b on a.djbh=b.xgdjbh join spkfk c on b.spid=c.spid join mchk d on a.dwbh=d.dwbh'
set @sqlstring=' where a.rq>='+@qsrq+' and a.rq<='+@jsrq+' and c.shpchd like ''%'+@shpchd+'%'''
set @sqllast=@sql+@sqlstring

就最后一步不正确

[青锋-SS]

修改后结果如下

declare @sql varchar(1000),@sqlstring varchar(1000),@sqllast varchar(8000)
set @sql='select a.rq,d.danwbh,d.dwmch,c.spbh,c.spmch,c.shpgg,c.shpchd,b.shl,c.dw,b.pihao,b.sxrq from jxdjhz a join ywmxk b on a.djbh=b.xgdjbh join spkfk c on b.spid=c.spid join mchk d on a.dwbh=d.dwbh'
set @sqlstring=' where a.rq> =convert(char(8),dateadd(month,-1,getdate()),120)+''26'' and a.rq <=convert(char(8),dateadd(month,-1,getdate()),120)+''25'' and c.shpchd like '' %西安杨森制药有限公司%'''
set @sqllast=@sql+@sqlstring
print @sqllast

select a.rq,d.danwbh,d.dwmch,c.spbh,c.spmch,c.shpgg,c.shpchd,b.shl,c.dw,b.pihao,b.sxrq from jxdjhz a join ywmxk b on a.djbh=b.xgdjbh join spkfk c on b.spid=c.spid join mchk d on a.dwbh=d.dwbh where a.rq> =convert(char(8),dateadd(month,-1,getdate()),120)+'26' and a.rq <=convert(char(8),dateadd(month,-1,getdate()),120)+'25' and c.shpchd like ' %西安杨森制药有限公司%'

[青锋-SS]

把定义@sqllast的长度加长就可以了

[kk19840210]

你SQL 设置的问题 SQL 默认没列显示 255个字符的

如果要修改

选项中找到 没列中显示的最大字符数 修改下
再 查看

[kelph]

是不是查询分析器select的问题？

换成print @sqllast ，你看结果正确吧

或者查询分析器工具-> 选项-> 结果,每列最多字符数改为4000

[netcup]

哥们,不是长度的问题,SQLLAST的长度最度也不到1000

25或者26 是求上月25日到本月26日,因为每个月都要查询,是动态的,所以用动态语句作为数值插入表,然后其他语句提取表中的开始时间和结束时间作为条件

[netcup]

恩,飞天小虫的解释有道理,估计有可能是这个.
不过,这些都是在存储过程里执行的
马上测试下

[kk19840210]

2000修改 工具-选项-结果-每列最多字符数
2005修改 工具-选项-查询结果-SQL SERVER-以文本格式显示结果-每列中显示最大字符数

[netcup]

'select a.rq,d.danwbh,d.dwmch,c.spbh,c.spmch,c.shpgg,c.shpchd,b.shl,c.dw,b.pihao,b.sxrq from jxdjhz a join ywmxk b on a.djbh=b.xgdjbh join spkfk c on b.spid=c.spid join mchk d on a.dwbh=d.dwbh where a.rq> =convert(char(8),dateadd(month,-1,getdate()),120)+'2'

把每列字符数改为1000,依旧显示上面的,还是截断
晕,还是不行啊
我是把@SQLLAST这个变量做为邹老大的导出excel文件的sqlstr查询语句数值的.但在set @sqllast=@sql+@sqlstring时总被截断

[青锋-SS]

根据楼主上面提供的语句,执行结果没发现问题

declare @qsrq varchar(60),@jsrq varchar(100),@shpchd varchar(1000)
declare @sql varchar(100),@sqlstring varchar(1000),@sqllast varchar(2000)

select @qsrq=qsrq,@jsrq=jsrq,@shpchd=shpchd from email_list where datepart(day,getdate())=aday and datepart(hour,getdate())=ahour
--上面的查询没有结果,用以下赋值代替
set @qsrq='26'
set @jsrq='25'
set @shpchd='asdfasd'

set @sql='select a.rq,d.danwbh,d.dwmch,c.spbh,c.spmch,c.shpgg,c.shpchd,b.shl,c.dw,b.pihao,b.sxrq from jxdjhz a join ywmxk b on a.djbh=b.xgdjbh join spkfk c on b.spid=c.spid join mchk d on a.dwbh=d.dwbh' 
set @sqlstring=' where a.rq>='+@qsrq+' and a.rq<='+@jsrq+' and c.shpchd like ''%'+@shpchd+'%''' 
set @sqllast=@sql+@sqlstring
print @sqllast


--执行结果
select a.rq,d.danwbh,d.dwmch,c.spbh,c.spmch,c.shpgg,c.shpchd,b.shl,c.dw,b.pihao,b.sxrq from jxdjhz a where a.rq>=26 and a.rq<=25 and c.shpchd like '%asdfasd%'

[青锋-SS]

上面的定义少写了个0,更正如下:

declare @qsrq varchar(60),@jsrq varchar(100),@shpchd varchar(1000)
declare @sql varchar(1000),@sqlstring varchar(1000),@sqllast varchar(2000)

select @qsrq=qsrq,@jsrq=jsrq,@shpchd=shpchd from email_list where datepart(day,getdate())=aday and datepart(hour,getdate())=ahour
--上面的查询没有结果,用以下赋值代替
set @qsrq='26'
set @jsrq='25'
set @shpchd='asdfasd'

set @sql='select a.rq,d.danwbh,d.dwmch,c.spbh,c.spmch,c.shpgg,c.shpchd,b.shl,c.dw,b.pihao,b.sxrq from jxdjhz a join ywmxk b on a.djbh=b.xgdjbh join spkfk c on b.spid=c.spid join mchk d on a.dwbh=d.dwbh' 
set @sqlstring=' where a.rq>='+@qsrq+' and a.rq<='+@jsrq+' and c.shpchd like ''%'+@shpchd+'%''' 
set @sqllast=@sql+@sqlstring
print @sqllast

--结果
select a.rq,d.danwbh,d.dwmch,c.spbh,c.spmch,c.shpgg,c.shpchd,b.shl,c.dw,b.pihao,b.sxrq from jxdjhz a join ywmxk b on a.djbh=b.xgdjbh join spkfk c on b.spid=c.spid join mchk d on a.dwbh=d.dwbh where a.rq>=26 and a.rq<=25 and c.shpchd like '%asdfasd%'

[netcup]

谢谢楼上各位,最终确诊确实是SQL每列设置字符大于256了.改正好了.谢谢飞天小虫和KELPH.结贴