积极答复者
存储过程 提示未定义变量@cCusCode

问题
-
--新建临时表,然后执行存储过程,这个是可以执行的
--但存在的问题是临时表数据并不是实时更新的
--所以需要将临时表建立、取数写在存储过程里面,这样就可以自动更新临时表数据
现在要写一个销售收入成本信息自定义报表,通过销售出库单取出客户、产品等信息,通过项目号进行关项目辅助账中取出价格数据,然后进行汇总
收入(含大修) 成本(含大修) 产品软件收入 总账-项目辅助账,会计科目:600101、600102、600103;605104 总账-项目辅助账.会计科目:640101、640103;640203 总账-项目辅助账.会计科目:600102 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
- 已编辑 jiebozhang 2013年12月13日 8:35 补充文字说明
答案
-
LZ,存储过程里面不能写GO语句的
在没有实际数据的情况下,只能帮您检查语法错误了
USE UFDATA_012_2011 GO --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 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
- 已标记为答案 jiebozhang 2013年12月16日 1:30
全部回复
-
LZ,存储过程里面不能写GO语句的
在没有实际数据的情况下,只能帮您检查语法错误了
USE UFDATA_012_2011 GO --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 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
- 已标记为答案 jiebozhang 2013年12月16日 1:30
-
桦仔说的很对,把GO 去掉。
As you suggest, you can't use the GO statement within a stored procedure. The GO command indicates the end of a batch of SQL Statements and a stored procedure is itself a batch of statements encapsulated as one routine.
You can be confident that each statement will run sequentially as this is the behaviour of SQL Server. So statements will not start until the previous statement has completed.
Please Mark As Answer if it is helpful.