近日写一个库存统计的存储过程,卡在数据转换这个问题上已经三天了,怎么都想不明白为什么错误,现把语句贴上来,求教高手,在线等CREATE PROCEDURE dbo.Pro_kctj
@yd int
AS
beginDECLARE
@startDate datetime,
@endDate datetime,
@adddays int
SELECT @adddays = 1 --日期增量
SELECT @startDate = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0); --年度的第一天
SELECT @endDate = getdate();--当前日期create table #tmpkcsj (scdate datetime,cpname char(20),scxname char(30),jzjs int,zdjs int,qckc int,scjs int,fjjs int,rkcjs int,rkcje float(8))
WHILE @startDate <= @endDate
BEGIN
SELECT @startDate AS scdate, dbo.Dqckc.cpname AS cpname, dbo.Dqckc.scxname AS scxname, ISNULL(MAX(dbo.Dqckc.jzjs), 0) AS jzjs, ISNULL(MAX(dbo.Dqckc.zdjs), 0) AS zdjs,
(SELECT dbo.Dqckcmx.qcjs FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate =(CASE WHEN
(SELECT MAX(dbo.Dqckcmx.creatdate) FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate <= @startDate) IS NULL THEN
(SELECT MIN(dbo.Dqckcmx.creatdate) FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate >= @startDate) ELSE
(SELECT MAX(dbo.Dqckcmx.creatdate) FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate <= @startDate) END)) AS qcjs,
ISNULL((SELECT SUM(dbo.Drsctj.scjs) FROM dbo.Drsctj WHERE dbo.Drsctj.cpname = dbo.Dqckc.cpname AND dbo.Drsctj.scdate BETWEEN @startDate AND @endDate), 0) AS scjs,
ISNULL((SELECT SUM(dbo.Drsctj.fjjs) FROM dbo.Drsctj WHERE dbo.Drsctj.cpname = dbo.Dqckc.cpname AND dbo.Drsctj.scdate BETWEEN @startDate AND @endDate), 0) AS fjjs,
((SELECT dbo.Dqckcmx.qcjs FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate = (CASE WHEN
(SELECT MAX(dbo.Dqckcmx.creatdate) FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate <= @startDate) IS NULL THEN
(SELECT MIN(dbo.Dqckcmx.creatdate) FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate >= @startDate) ELSE
(SELECT MAX(dbo.Dqckcmx.creatdate) FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate <= @startDate) END))+
ISNULL((SELECT SUM(dbo.Drsctj.scjs) FROM dbo.Drsctj WHERE dbo.Drsctj.cpname = dbo.Dqckc.cpname AND dbo.Drsctj.scdate BETWEEN @startDate AND @endDate),0)-
ISNULL ((SELECT SUM(dbo.Drsctj.fjjs) FROM dbo.Drsctj WHERE dbo.Drsctj.cpname = dbo.Dqckc.cpname AND dbo.Drsctj.scdate BETWEEN @startDate AND @endDate),0)) AS rkcjs,
ISNULL(MAX(dbo.Drsctj.bjdj), 0) *
((SELECT dbo.Dqckcmx.qcjs FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate = (CASE WHEN
(SELECT MAX(dbo.Dqckcmx.creatdate) FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate <= @startDate) IS NULL THEN
(SELECT MIN(dbo.Dqckcmx.creatdate) FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate >= @startDate) ELSE
(SELECT MAX(dbo.Dqckcmx.creatdate) FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate <= @startDate) END))+
ISNULL((SELECT SUM(dbo.Drsctj.scjs) FROM dbo.Drsctj WHERE dbo.Drsctj.cpname = dbo.Dqckc.cpname AND dbo.Drsctj.scdate BETWEEN @startDate AND @endDate),0)-
ISNULL ((SELECT SUM(dbo.Drsctj.fjjs) FROM dbo.Drsctj WHERE dbo.Drsctj.cpname = dbo.Dqckc.cpname AND dbo.Drsctj.scdate BETWEEN @startDate AND @endDate),0)) AS rkcje
FROM dbo.Drsctj RIGHT OUTER JOIN dbo.Dqckc ON dbo.Drsctj.cpname = dbo.Dqckc.cpname GROUP BY scdate, dbo.Dqckc.cpname, dbo.Dqckc.scxname;
insert into #tmpkcsj (scdate,cpname,scxname,jzjs,zdjs,qckc,scjs,fjjs,rkcjs,rkcje) values ('@startDate','cpname','scxname','jzjs','zdjs','qckc','scjs','fjjs','rkcjs',cast(Ltrim('kcje') as float));
SELECT @startDate = @startDate + @adddays;
ENDEND
GO如果注销掉insert into 语句后,可以出来结果,但结果是有多少日期就有多少张表,不方便后面统计,所以我想将循环查询的结果插入新的临时表中,以便后面调用,错误就出现在这个地方。请高手救命啊,我在线等哦
@yd int
AS
beginDECLARE
@startDate datetime,
@endDate datetime,
@adddays int
SELECT @adddays = 1 --日期增量
SELECT @startDate = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0); --年度的第一天
SELECT @endDate = getdate();--当前日期create table #tmpkcsj (scdate datetime,cpname char(20),scxname char(30),jzjs int,zdjs int,qckc int,scjs int,fjjs int,rkcjs int,rkcje float(8))
WHILE @startDate <= @endDate
BEGIN
SELECT @startDate AS scdate, dbo.Dqckc.cpname AS cpname, dbo.Dqckc.scxname AS scxname, ISNULL(MAX(dbo.Dqckc.jzjs), 0) AS jzjs, ISNULL(MAX(dbo.Dqckc.zdjs), 0) AS zdjs,
(SELECT dbo.Dqckcmx.qcjs FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate =(CASE WHEN
(SELECT MAX(dbo.Dqckcmx.creatdate) FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate <= @startDate) IS NULL THEN
(SELECT MIN(dbo.Dqckcmx.creatdate) FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate >= @startDate) ELSE
(SELECT MAX(dbo.Dqckcmx.creatdate) FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate <= @startDate) END)) AS qcjs,
ISNULL((SELECT SUM(dbo.Drsctj.scjs) FROM dbo.Drsctj WHERE dbo.Drsctj.cpname = dbo.Dqckc.cpname AND dbo.Drsctj.scdate BETWEEN @startDate AND @endDate), 0) AS scjs,
ISNULL((SELECT SUM(dbo.Drsctj.fjjs) FROM dbo.Drsctj WHERE dbo.Drsctj.cpname = dbo.Dqckc.cpname AND dbo.Drsctj.scdate BETWEEN @startDate AND @endDate), 0) AS fjjs,
((SELECT dbo.Dqckcmx.qcjs FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate = (CASE WHEN
(SELECT MAX(dbo.Dqckcmx.creatdate) FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate <= @startDate) IS NULL THEN
(SELECT MIN(dbo.Dqckcmx.creatdate) FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate >= @startDate) ELSE
(SELECT MAX(dbo.Dqckcmx.creatdate) FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate <= @startDate) END))+
ISNULL((SELECT SUM(dbo.Drsctj.scjs) FROM dbo.Drsctj WHERE dbo.Drsctj.cpname = dbo.Dqckc.cpname AND dbo.Drsctj.scdate BETWEEN @startDate AND @endDate),0)-
ISNULL ((SELECT SUM(dbo.Drsctj.fjjs) FROM dbo.Drsctj WHERE dbo.Drsctj.cpname = dbo.Dqckc.cpname AND dbo.Drsctj.scdate BETWEEN @startDate AND @endDate),0)) AS rkcjs,
ISNULL(MAX(dbo.Drsctj.bjdj), 0) *
((SELECT dbo.Dqckcmx.qcjs FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate = (CASE WHEN
(SELECT MAX(dbo.Dqckcmx.creatdate) FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate <= @startDate) IS NULL THEN
(SELECT MIN(dbo.Dqckcmx.creatdate) FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate >= @startDate) ELSE
(SELECT MAX(dbo.Dqckcmx.creatdate) FROM dbo.Dqckcmx WHERE dbo.Dqckcmx.cpname = dbo.Dqckc.cpname AND dbo.Dqckcmx.creatdate <= @startDate) END))+
ISNULL((SELECT SUM(dbo.Drsctj.scjs) FROM dbo.Drsctj WHERE dbo.Drsctj.cpname = dbo.Dqckc.cpname AND dbo.Drsctj.scdate BETWEEN @startDate AND @endDate),0)-
ISNULL ((SELECT SUM(dbo.Drsctj.fjjs) FROM dbo.Drsctj WHERE dbo.Drsctj.cpname = dbo.Dqckc.cpname AND dbo.Drsctj.scdate BETWEEN @startDate AND @endDate),0)) AS rkcje
FROM dbo.Drsctj RIGHT OUTER JOIN dbo.Dqckc ON dbo.Drsctj.cpname = dbo.Dqckc.cpname GROUP BY scdate, dbo.Dqckc.cpname, dbo.Dqckc.scxname;
insert into #tmpkcsj (scdate,cpname,scxname,jzjs,zdjs,qckc,scjs,fjjs,rkcjs,rkcje) values ('@startDate','cpname','scxname','jzjs','zdjs','qckc','scjs','fjjs','rkcjs',cast(Ltrim('kcje') as float));
SELECT @startDate = @startDate + @adddays;
ENDEND
GO如果注销掉insert into 语句后,可以出来结果,但结果是有多少日期就有多少张表,不方便后面统计,所以我想将循环查询的结果插入新的临时表中,以便后面调用,错误就出现在这个地方。请高手救命啊,我在线等哦
将数据类型 varchar 转换为 numeric 时出错。如果大家看得晕我就把中间的过程简化一下,其实中间就是一个查询并计算的过程,查询出来的结果插入临时表:CREATE PROCEDURE dbo.Pro_kctj
@yd int
AS
beginDECLARE
@startDate datetime,
@endDate datetime,
@adddays int
SELECT @adddays = 1 --日期增量
SELECT @startDate = DATEADD(yy, DATEDIFF(yy,0,getdate()), 0); --年度的第一天
SELECT @endDate = getdate();--当前日期create table #tmpkcsj (scdate datetime,cpname char(20),scxname char(30),jzjs int,zdjs int,qckc int,scjs int,fjjs int,rkcjs int,rkcje float(8))
WHILE @startDate <= @endDate
BEGIN
SELECT @startDate AS scdate,
dbo.Dqckc.cpname AS cpname,
dbo.Dqckc.scxname AS scxname,
ISNULL(MAX(dbo.Dqckc.jzjs), 0) AS jzjs,
ISNULL(MAX(dbo.Dqckc.zdjs), 0) AS zdjs,
ISNULL(MAX(dbo.Dqckc.qckc), 0) AS qcjs,
ISNULL(SUM(dbo.Drsctj.scjs) FROM dbo.Drsctj WHERE dbo.Drsctj.cpname = dbo.Dqckc.cpname AND dbo.Drsctj.scdate BETWEEN @startDate AND @endDate), 0) AS scjs,
ISNULL((SELECT SUM(dbo.Drsctj.fjjs) FROM dbo.Drsctj WHERE dbo.Drsctj.cpname = dbo.Dqckc.cpname AND dbo.Drsctj.scdate BETWEEN @startDate AND @endDate), 0) AS fjjs,
ISNULL(MAX(dbo.Dqckc.qckc), 0) AS qcjs+
ISNULL((SELECT SUM(dbo.Drsctj.scjs) FROM dbo.Drsctj WHERE dbo.Drsctj.cpname = dbo.Dqckc.cpname AND dbo.Drsctj.scdate BETWEEN @startDate AND @endDate),0)-
ISNULL ((SELECT SUM(dbo.Drsctj.fjjs) FROM dbo.Drsctj WHERE dbo.Drsctj.cpname = dbo.Dqckc.cpname AND dbo.Drsctj.scdate BETWEEN @startDate AND @endDate),0)) AS rkcjs,
ISNULL(MAX(dbo.Drsctj.bjdj), 0) *
ISNULL(MAX(dbo.Dqckc.qckc), 0) AS qcjs+
ISNULL((SELECT SUM(dbo.Drsctj.scjs) FROM dbo.Drsctj WHERE dbo.Drsctj.cpname = dbo.Dqckc.cpname AND dbo.Drsctj.scdate BETWEEN @startDate AND @endDate),0)-
ISNULL ((SELECT SUM(dbo.Drsctj.fjjs) FROM dbo.Drsctj WHERE dbo.Drsctj.cpname = dbo.Dqckc.cpname AND dbo.Drsctj.scdate BETWEEN @startDate AND @endDate),0)) AS rkcje
FROM dbo.Drsctj RIGHT OUTER JOIN dbo.Dqckc ON dbo.Drsctj.cpname = dbo.Dqckc.cpname GROUP BY scdate, dbo.Dqckc.cpname, dbo.Dqckc.scxname; insert into #tmpkcsj (scdate,cpname,scxname,jzjs,zdjs,qckc,scjs,fjjs,rkcjs,rkcje) values ('@startDate','cpname','scxname','jzjs','zdjs','qckc','scjs','fjjs','rkcjs','kcje') ; SELECT @startDate = @startDate + @adddays;ENDEND
GO
2011-1-1 *** 12 ***
2011-1-1 *** 24 ***
***
(结果插入临时表)第二个循序查询出来的结果为:
2011-1-2 *** 34 ***
2011-1-2 *** 56 ***
***
(结果插入临时表)***我就想把这些数据插入临时表,表现为数据类型错误,按道理这些都是隐式转换的,不需要专门转换数据啊。
另外:我用的是SQL SERVER 2000 企业版拜托大家看看,谢谢
@startdate cpname scxname qcjs jzjs zdjs ***
@startdate cpname scxname qcjs jzjs zdjs ***
***看来是循环过程中根本没有取得查询的数据,而是将列名作为结果插入到临时表中了,谢谢ssp2009的热心回复。