CREATE PROC NC_RPT
@MON VARCHAR(6),@B datetime
AS
SET ANSI_NULLS ON
SET ANSI_NULLS ON
DECLARE @QF VARCHAR(8000)
select @B=acct_month from acct_month where acct_month1='+CAST(@MON AS VARCHAR)+'
/*这里acct_month 表中有两个字段,分别是acct_month datetime型 和acct_month1字符型,为的是能够让用户输入
一个年月比如'200901' 而得到一个datetime型的值赋给@B,其目的是为了能够使用下面语句中的dateadd函数为其做年月的加减*/
SET @QF='
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME=''NCRPT2_'+CAST(@MON AS VARCHAR)+''')
BEGIN
DROP TABLE NCRPT2_'+CAST(@MON AS VARCHAR)+'
select @B=acct_month from acct_month where acct_month1='+CAST(@MON AS VARCHAR)+'
SELECT REGION_NAME,sum(charge) AS [B2]
INTO NCRPT2_'+CAST(@MON AS VARCHAR)+' FROM
acct_item_'+CAST(@MON AS VARCHAR)+'_t where acct_month=CONVERT(VARCHAR(6),DATEADD(MONTH,-1,@B),112)
group by region_name
END
ELSE
SELECT REGION_NAME,sum(charge) AS [B2]
INTO NCRPT2_'+CAST(@MON AS VARCHAR)+' FROM
acct_item_'+CAST(@MON AS VARCHAR)+'_t where acct_month=CONVERT(VARCHAR(6),DATEADD(MONTH,-1,@B),112)
group by region_name
'
EXEC(@QF)
GO
存储过程可以正常创建,但是在运行该存储过程
EXEC NC_RPT '200901'
的时候会出现以下的错误,请问大家有没有办法帮我看看是怎么回事,其实
服务器: 消息 201,级别 16,状态 4,过程 NC_RPTB,行 0
过程 'NC_RPTB' 需要参数 '@B',但未提供该参数。CONVERT(VARCHAR(6),DATEADD(MONTH,-1,@B),112) 这段我的目的就是为了能够在输入 200901 的时候得到200812
CREATE PROC NC_RPT
@MON VARCHAR(6),@B datetime = NULL
AS
SET ANSI_NULLS ON
SET ANSI_NULLS ON
DECLARE @QF VARCHAR(8000)
select @B=acct_month from acct_month where acct_month1='+CAST(@MON AS VARCHAR)+'
/*这里acct_month 表中有两个字段,分别是acct_month datetime型 和acct_month1字符型,为的是能够让用户输入
一个年月比如'200901' 而得到一个datetime型的值赋给@B,其目的是为了能够使用下面语句中的dateadd函数为其做年月的加减*/
SET @QF='
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME=''NCRPT2_'+CAST(@MON AS VARCHAR)+''')
BEGIN
DROP TABLE NCRPT2_'+CAST(@MON AS VARCHAR)+'
select @B=acct_month from acct_month where acct_month1='+CAST(@MON AS VARCHAR)+'
SELECT REGION_NAME,sum(charge) AS [B2]
INTO NCRPT2_'+CAST(@MON AS VARCHAR)+' FROM
acct_item_'+CAST(@MON AS VARCHAR)+'_t where acct_month=CONVERT(VARCHAR(6),DATEADD(MONTH,-1,@B),112)
group by region_name
END
ELSE
SELECT REGION_NAME,sum(charge) AS [B2]
INTO NCRPT2_'+CAST(@MON AS VARCHAR)+' FROM
acct_item_'+CAST(@MON AS VARCHAR)+'_t where acct_month=CONVERT(VARCHAR(6),DATEADD(MONTH,-1,@B),112)
group by region_name
'
EXEC(@QF)
GO
CREATE PROC NC_RPT
@MON VARCHAR(6)
AS
BEGIN
DECLARE @B datetime
SET @B = NULL
...
END
另一种方式,在调用的时候多加一个参数@B的值.
EXEC NC_RPT @MON='200901',@B=GETDATE()
:必须声明变量 '@B'。 的错误。CREATE PROC NC_RPT
@MON VARCHAR(6)
AS
SET ANSI_NULLS ON
SET ANSI_NULLS ON
BEGIN
DECLARE @B datetime
SET @B = NULL
select @B=acct_month from acct_month where acct_month1='+CAST(@MON AS VARCHAR)+'
END
DECLARE @QF VARCHAR(8000)
SET @QF='
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME=''NCRPT2_'+CAST(@MON AS VARCHAR)+''')
BEGIN
DROP TABLE NCRPT2_'+CAST(@MON AS VARCHAR)+'
select @B=acct_month from acct_month where acct_month1='+CAST(@MON AS VARCHAR)+'
SELECT REGION_NAME,sum(charge) AS [B2]
INTO NCRPT2_'+CAST(@MON AS VARCHAR)+' FROM
acct_item_'+CAST(@MON AS VARCHAR)+'_t where acct_month=CONVERT(VARCHAR(6),DATEADD(MONTH,-1,@B),112)
group by region_name
END
ELSE
SELECT REGION_NAME,sum(charge) AS [B2]
INTO NCRPT2_'+CAST(@MON AS VARCHAR)+' FROM
acct_item_'+CAST(@MON AS VARCHAR)+'_t where acct_month=CONVERT(VARCHAR(6),DATEADD(MONTH,-1,@B),112)
group by region_name
'
EXEC(@QF)
GO