存储过程中字符串截断问题

[专注研发ERP低代码开发平台]

declare @sql nvarchar(3000)
set @sql='select *,(eCostPrice+hCostPrice+mCostPrice) as TotalCostPrice from (select isnull(A.Batch,B.Batch) as Batch,isnull(A.MOID,B.MOID) as MOID,isnull(A.Date,B.Date) as Date,isnull(A.PMCPlanner,B.PMCPlanner) as PMCPlanner,isnull(A.PMCPlannerName,B.PMCPlannerName) as PMCPlannerName,isnull(A.eCostPrice,0) as eCostPrice,isnull(A.hCostPrice,0) as hCostPrice,isnull(B.mCostPrice,0) as mCostPrice from (select isnull(A.Batch,B.Batch) as Batch,isnull(A.MOID,B.MOID) as MOID,isnull(A.Date,B.Date) as Date,isnull(A.PMCPlanner,B.PMCPlanner) as PMCPlanner,isnull(A.PMCPlannerName,B.PMCPlannerName) as PMCPlannerName,A.eCostPrice,B.hCostPrice from #t_equ_cost A full outer join #t_hum_cost B on A.Batch=B.Batch) A full outer join #t_met_cost B on A.Batch=B.Batch) A'

出错错误指示

Error Message: 名称 'select *,(eCostPrice+hCostPrice+mCostPrice) as TotalCostPrice from (select isnull(A.Batch,B.Batch) as Batch,isnull(A.MOID,B.MOID) as MOID,isnull(A.Date,B.Date) as Date,isnull(A.PMCPlanner,B.PMCPlanner) as PMCPlanner,isnull(A.PMCPlannerName,B.PMCPlannerName) as PMCPlannerName,isnull(A.eCostPrice,0) as eCostPrice,isnull(A.hCostPrice,0) as hCostPrice,isnull(B.mCostPrice,0) as mCostPrice from (select isnull(A.Batch,B.Batch) as Batch,isnull(A.MOID,B.MOID) as MOID,isnull(A.Date,B.Date) as Date,isnull(A.PMCPlanner,B.PMCPlanner) as PMCPlanner,isnull(A.PMCPlannerName,B.PMCPlannerName) as PMCPlannerName,A.eCostPrice,B.hCostPrice from #t_equ_cost ' 不是有效的标识符


语句在红色位置被截断了，@sql长度设置为8000都无用。请问大家要怎么解决呢？

[soft_wsx]

nvarchar(4000)最大支持4000

[soft_wsx]

改成4000试试

[--小F--]

declare @sql nvarchar(3000) 
set @sql='select *,(eCostPrice+hCostPrice+mCostPrice) as TotalCostPrice from (select isnull(A.Batch,B.Batch) as Batch,isnull(A.MOID,B.MOID) as MOID,isnull(A.Date,B.Date) as Date,isnull(A.PMCPlanner,B.PMCPlanner) as PMCPlanner,isnull(A.PMCPlannerName,B.PMCPlannerName) as PMCPlannerName,isnull(A.eCostPrice,0) as eCostPrice,isnull(A.hCostPrice,0) as hCostPrice,isnull(B.mCostPrice,0) as mCostPrice from (select isnull(A.Batch,B.Batch) as Batch,isnull(A.MOID,B.MOID) as MOID,isnull(A.Date,B.Date) as Date,isnull(A.PMCPlanner,B.PMCPlanner) as PMCPlanner,isnull(A.PMCPlannerName,B.PMCPlannerName) as PMCPlannerName,A.eCostPrice,B.hCostPrice from #t_equ_cost A full outer join #t_hum_cost B on A.Batch=B.Batch) A full outer join #t_met_cost B on A.Batch=B.Batch) A'

print(@sql)
/*select *,
(eCostPrice+hCostPrice+mCostPrice) as TotalCostPrice 
from (select isnull(A.Batch,B.Batch) as Batch,isnull(A.MOID,B.MOID) as MOID,
isnull(A.Date,B.Date) as Date,isnull(A.PMCPlanner,B.PMCPlanner) as PMCPlanner,
isnull(A.PMCPlannerName,B.PMCPlannerName) as PMCPlannerName,
isnull(A.eCostPrice,0) as eCostPrice,isnull(A.hCostPrice,0) as hCostPrice,
isnull(B.mCostPrice,0) as mCostPrice from (select isnull(A.Batch,B.Batch) as Batch,
isnull(A.MOID,B.MOID) as MOID,
isnull(A.Date,B.Date) as Date,
isnull(A.PMCPlanner,B.PMCPlanner) as PMCPlanner,
isnull(A.PMCPlannerName,B.PMCPlannerName) as PMCPlannerName,
A.eCostPrice,B.hCostPrice from #t_equ_cost A 
full outer join #t_hum_cost B on A.Batch=B.Batch) A 
full outer join #t_met_cost B on A.Batch=B.Batch) A*/

[--小F--]

偶机器上是好的

[SQL77]

看得晕了!!!!
将语句分好,长度应该没问题,没超过3000

[soft_wsx]

如果再不行的话，多加两个变量，最后将变量相加起来

[专注研发ERP低代码开发平台]

[Quote=引用 3 楼 fredrickhu 的回复:]
SQL codedeclare@sqlnvarchar(3000)set@sql='select *,(eCostPrice+hCostPrice+mCostPrice) as TotalCostPrice from (select isnull(A.Batch,B.Batch) as Batch,isnull(A.MOID,B.MOID) as MOID,isnull(A.Date,B.Date¡­
[/Quote]

我的直接打印也是好的，但到程序中招行就不行了。 郁闷啊。。

我的最开始的存储过程。。大家帮我看看问题在那好不？


ALTER proc [dbo].[e_pro_cost]
@type tinyint
as
--将机台信息存入临时表
begin
if exists(select * from tempdb.dbo.sysobjects where id=object_id(N'tempdb.dbo.#t_equ_cost'))
drop table #t_equ_cost
create table #t_equ_cost
(
Batch varchar(50),
eCostPrice float,
MOID varchar(50),
Date Datetime,
PMCPlanner varchar(50),
PMCPlannerName varchar(50)
)
insert into #t_equ_cost exec e_equ_cost @type
end

--将人工信息存入临时表
begin
if exists(select * from tempdb.dbo.sysobjects where id=object_id(N'tempdb.dbo.#t_hum_cost'))
drop table #t_hum_cost
create table #t_hum_cost
(
Batch varchar(50),
hCostPrice float,
MOID varchar(50),
Date Datetime,
PMCPlanner varchar(50),
PMCPlannerName varchar(50)
)
insert into #t_hum_cost exec e_hum_cost @type
end

--将物料信息存入临时表
begin
if exists(select * from tempdb.dbo.sysobjects where id=object_id(N'tempdb.dbo.#t_met_cost'))
drop table #t_met_cost
create table #t_met_cost
(
Batch varchar(50),
mCostPrice float,
MOID varchar(50),
Date Datetime,
PMCPlanner varchar(50),
PMCPlannerName varchar(50)
)
insert into #t_met_cost exec e_met_cost @type
end

--机台，人工，物料总和
declare @sql varchar(8000)
set @sql='select *,(eCostPrice+hCostPrice+mCostPrice) as TotalCostPrice from (
select
isnull(A.Batch,B.Batch) as Batch,
isnull(A.MOID,B.MOID) as MOID,
isnull(A.Date,B.Date) as Date,
isnull(A.PMCPlanner,B.PMCPlanner) as PMCPlanner,
isnull(A.PMCPlannerName,B.PMCPlannerName) as PMCPlannerName,
isnull(A.eCostPrice,0) as eCostPrice,
isnull(A.hCostPrice,0) as hCostPrice,
isnull(B.mCostPrice,0) as mCostPrice from
(
select
isnull(A.Batch,B.Batch) as Batch,
isnull(A.MOID,B.MOID) as MOID,
isnull(A.Date,B.Date) as Date,
isnull(A.PMCPlanner,B.PMCPlanner) as PMCPlanner,
isnull(A.PMCPlannerName,B.PMCPlannerName) as PMCPlannerName,
A.eCostPrice,B.hCostPrice from #t_equ_cost A full outer join #t_hum_cost B on A.Batch=B.Batch
)
A full outer join #t_met_cost B on A.Batch=B.Batch
) A'

execute @sql

drop table #t_met_cost
drop table #t_hum_cost
drop table #t_equ_cost
GO

[Tomzzu]

问题不在于长度
红色位置被截断了, 并不是实际字符串被截断, 而是错误提示信息有个字串的最大限制, 它只是截断限制内的字串提示给你而已

根据错误信息显示 (Error Message: 名称), 数据库把它当然字段名称了

先别用拼串, 用实际语句运行一下试试

[sdhdy]

[Quote=引用 8 楼 tomzzu 的回复:]
问题不在于长度
红色位置被截断了, 并不是实际字符串被截断, 而是错误提示信息有个字串的最大限制, 它只是截断限制内的字串提示给你而已

根据错误信息显示 (Error Message: 名称), 数据库把它当然字段名称了

先别用拼串, 用实际语句运行一下试试
[/Quote]
.......

[专注研发ERP低代码开发平台]

[Quote=引用 9 楼 sdhdy 的回复:]
引用 8 楼 tomzzu 的回复:
问题不在于长度
红色位置被截断了, 并不是实际字符串被截断, 而是错误提示信息有个字串的最大限制, 它只是截断限制内的字串提示给你而已

根据错误信息显示 (Error Message: 名称), 数据库把它当然字段名称了

先别用拼串, 用实际语句运行一下试试

.......
[/Quote]

实际招行OK的，只是我现在要加给存储过程加一个iswhere参数作为搜索条件，就要用拼串了。

[专注研发ERP低代码开发平台]

对不起大家，我把execute (@sql) 写成 execute @sql 了。。。 郁闷！

[feixianxxx]

[Quote=引用 11 楼 wggfcusmq 的回复:]
对不起大家，我把execute (@sql) 写成 execute @sql 了。。。 郁闷！
[/Quote]
...
问题解决了?

[--小F--]

[Quote=引用 11 楼 wggfcusmq 的回复:]
对不起大家，我把execute (@sql) 写成 execute @sql 了。。。 郁闷！
[/Quote]
OMG

[--小F--]

用EXEC不行么？

[ChinaJiaBing]

能运行过去..你是什么数据库..2000

[专注研发ERP低代码开发平台]

[Quote=引用 12 楼 feixianxxx 的回复:]
引用 11 楼 wggfcusmq 的回复:
对不起大家，我把execute (@sql) 写成 execute @sql 了。。。 郁闷！

...
问题解决了?
[/Quote]

解决了。。谢谢～

[专注研发ERP低代码开发平台]

[Quote=引用 15 楼 chinajiabing 的回复:]
能运行过去..你是什么数据库..2000
[/Quote]

我是用2005啊