关于变量赋值的问题，请教大家！

[ncufunk]

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

[sdhdy]

存储过程有2个参数，你只付了一个。

[sdhdy]

这个,@B，你没赋值。

[長胸為富]

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

[usher_gml]

少了个参数

[hery2002]

一种方式,去掉参数@B,

CREATE PROC NC_RPT
@MON VARCHAR(6)
AS
BEGIN
   DECLARE @B datetime 
   SET @B = NULL
   ...
END

另一种方式,在调用的时候多加一个参数@B的值.

EXEC NC_RPT @MON='200901',@B=GETDATE()

[htl258_Tony]

可以把@B设定一个默认值,那调用的时候可以只用一个参数.

[ncufunk]

ls的高手，我把它给改成下面这样的提示
：必须声明变量 '@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

[SQL77]

[Quote=引用 7 楼 ncufunk 的回复:]
ls的高手，我把它给改成下面这样的提示
：必须声明变量 '@B'。　的错误。

SQL code
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(@…
[/Quote]

你后面有用@B呀,呵呵

[SQL77]

[Quote=引用 7 楼 ncufunk 的回复:]
ls的高手，我把它给改成下面这样的提示
：必须声明变量 '@B'。　的错误。

SQL code
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(@…
[/Quote]

高手们都说了,给@B赋一个默认值!!

[cxmcxm]

存储过程2参数，你只设置了一个.

[claro]

帮顶