存储过程提示未定义变量@cCusCode
USE UFDATA_012_2011
--exec usp_zhangjie
--@cCusCode='全部',
--@start='2013-01-01',
--@end='2013-12-31',
--@cPersonCode='全部',
--@cDepCode='全部',
--@csocode='全部',
--@strCode='全部',
--@cInvCode='全部'
GO
/****** Object: StoredProcedure [dbo].[usp_zhangjie] Script Date: 12/08/2013 17:28:45 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
--建立销售收入信息表
GO
alter proc [dbo].[usp_zhangjie]
@cCusCode varchar(50), --客户编码
@start datetime, --开始日期
@end datetime, --结束日期
@cPersonCode varchar(50), --业务员编码
@cDepCode varchar(50), --部门编码
@csocode varchar(50), --需求跟踪号
@strCode varchar(50), --合同编码
@cInvCode varchar(50) --产品编码
as
BEGIN
--建立销售收入信息表
IF OBJECT_ID('tempdb..#temp_ccodeIN') IS NOT NULL
DROP TABLE #temp_ccodeIN;
GO
CREATE TABLE #temp_ccodeIN(
citem_id VARCHAR(255) NULL, --项目号
md money NULL --支出金额
)
INSERT INTO #temp_ccodeIN(citem_id,md)
SELECT g.citem_id,sum(g.md)
FROM GL_accass G
WHERE g.ccode in ('600101','600102','600103','605104')
group BY g.citem_id
--建立销售成本信息表
GO
IF OBJECT_ID('tempdb..#temp_ccodeout') IS NOT NULL
DROP TABLE #temp_ccodeout;
GO
CREATE TABLE #temp_ccodeout(
citem_id VARCHAR(255) NULL, --项目号
md money NULL --支出金额
)
INSERT INTO #temp_ccodeout(citem_id,md)
SELECT g.citem_id,sum(g.md)
FROM GL_accass G
WHERE g.ccode in ('640101','640103','640203')
group BY g.citem_id
--建立软件成本信息表
GO
IF OBJECT_ID('tempdb..#temp_ccodesoftware') IS NOT NULL
DROP TABLE #temp_ccodesoftware;
GO
CREATE TABLE #temp_ccodesoftware(
citem_id VARCHAR(255) NULL, --项目号
md money NULL --支出金额
)
INSERT INTO #temp_ccodesoftware(citem_id,md)
SELECT g.citem_id,sum(g.md)
FROM GL_accass G
WHERE g.ccode in ('600102')
group BY g.citem_id
--存储过程开始
SELECT A.cCusCode,h.[md],i.[md],j.[md],A.cPersonCode,A.dnverifytime,A.cDepCode,b.csocode,B.strCode,B.cInvCode,
c.cinvname,d.cdepname,e.cpersonname,f.cCusname
FROM RDRECORD A
INNER JOIN RDRECORDS B
ON A.ID=B.ID
AND A.cVouchType=32
inner join inventory c
on b.cinvcode = c.cinvcode
left outer join department d
on a.cdepcode = d.cdepcode
left outer join person e
on a.cpersoncode = e.cpersoncode
inner join Customer f
on a.cCusCode=f.cCusCode
left outer JOIN #temp_ccodein H
ON H.citem_id=B.cItemCode
left outer JOIN #temp_ccodeout I
ON I.citem_id=B.cItemCode
left outer JOIN #temp_ccodesoftware j
ON j.citem_id=B.cItemCode
where (@cCusCode = '全部' OR a.cCusCode = @cCusCode) and (a.dnverifytime >= @start and a.dnverifytime <= @end) and
(@cPersonCode = '全部' or a.cpersoncode = @cPersonCode) and (@cDepCode = '全部' or a.cDepCode = @cDepCode) and
(@csocode = '全部'or b.csocode = @csocode) and (@strCode = '全部' or b.strCode = @strCode) and (@cInvCode = '全部' or b.cInvCode = @cInvCode)
end
[解决办法]
alter PROC [dbo].[usp_zhangjie]
@cCusCode VARCHAR(50) , --客户编码
@start DATETIME , --开始日期
@end DATETIME , --结束日期
@cPersonCode VARCHAR(50) , --业务员编码
@cDepCode VARCHAR(50) , --部门编码
@csocode VARCHAR(50) , --需求跟踪号
@strCode VARCHAR(50) , --合同编码
@cInvCode VARCHAR(50) --产品编码
AS
BEGIN
--建立销售收入信息表
IF OBJECT_ID('tempdb..#temp_ccodeIN') IS NOT NULL
DROP TABLE #temp_ccodeIN;
CREATE TABLE #temp_ccodeIN
(
citem_id VARCHAR(255) NULL , --项目号
md MONEY NULL --支出金额
)
INSERT INTO #temp_ccodeIN
( citem_id ,
md
)
SELECT g.citem_id ,
SUM(g.md)
FROM GL_accass G
WHERE g.ccode IN ( '600101', '600102', '600103', '605104' )
GROUP BY g.citem_id
--建立销售成本信息表
IF OBJECT_ID('tempdb..#temp_ccodeout') IS NOT NULL
DROP TABLE #temp_ccodeout;
CREATE TABLE #temp_ccodeout
(
citem_id VARCHAR(255) NULL , --项目号
md MONEY NULL --支出金额
)
INSERT INTO #temp_ccodeout
( citem_id ,
md
)
SELECT g.citem_id ,
SUM(g.md)
FROM GL_accass G
WHERE g.ccode IN ( '640101', '640103', '640203' )
GROUP BY g.citem_id
--建立软件成本信息表
IF OBJECT_ID('tempdb..#temp_ccodesoftware') IS NOT NULL
DROP TABLE #temp_ccodesoftware;
CREATE TABLE #temp_ccodesoftware
(
citem_id VARCHAR(255) NULL , --项目号
md MONEY NULL --支出金额
)
INSERT INTO #temp_ccodesoftware
( citem_id ,
md
)
SELECT g.citem_id ,
SUM(g.md)
FROM GL_accass G
WHERE g.ccode IN ( '600102' )
GROUP BY g.citem_id
--存储过程开始
SELECT A.cCusCode ,
h.[md] ,
i.[md] ,
j.[md] ,
A.cPersonCode ,
A.dnverifytime ,
A.cDepCode ,
b.csocode ,
B.strCode ,
B.cInvCode ,
c.cinvname ,
d.cdepname ,
e.cpersonname ,
f.cCusname
FROM RDRECORD A
INNER JOIN RDRECORDS B ON A.ID = B.ID
AND A.cVouchType = 32
INNER JOIN inventory c ON b.cinvcode = c.cinvcode
LEFT OUTER JOIN department d ON a.cdepcode = d.cdepcode
LEFT OUTER JOIN person e ON a.cpersoncode = e.cpersoncode
INNER JOIN Customer f ON a.cCusCode = f.cCusCode
LEFT OUTER JOIN #temp_ccodein H ON H.citem_id = B.cItemCode
LEFT OUTER JOIN #temp_ccodeout I ON I.citem_id = B.cItemCode
LEFT OUTER JOIN #temp_ccodesoftware j ON j.citem_id = B.cItemCode
WHERE ( @cCusCode = '全部'
OR a.cCusCode = @cCusCode
)
AND ( a.dnverifytime >= @start
AND a.dnverifytime <= @end
)
AND ( @cPersonCode = '全部'
OR a.cpersoncode = @cPersonCode
)
AND ( @cDepCode = '全部'
OR a.cDepCode = @cDepCode
)
AND ( @csocode = '全部'
OR b.csocode = @csocode
)
AND ( @strCode = '全部'
OR b.strCode = @strCode
)
AND ( @cInvCode = '全部'
OR b.cInvCode = @cInvCode
)
END
