我已经尝试过很多exec proc ...
,但每次都会发生新的错误。
该过程是创建起来没有错误的,但问题是当我尝试执行它时。
CREATE PROCEDURE PRC1(@Card_code bigint, @Months dateTIME OUT,@Charge
decimal(10,2))
AS
BEGIN
DECLARE CRS CURSOR
FOR
SELECT 0.01*t.charge AS
firstTENDAYS,cc.Card_code,datepart(MONTH,t.Trans_time_date) AS NMONTH
from TRANSSACTION t join CREDIT_CARD cc on t.Card_code=cc.Card_code
where datepart(day,t.trans_time_date) between 0 and 10 and
@Card_code=CONVERT(int,cc.Card_code ) and
@MonthS=datepart(MONTH,t.Trans_time_date) and
@Charge=t.Charge
SELECT 0.03*t.charge AS
thirdTENDAYS,cc.Card_code,datepart(MONTH,t.Trans_time_date) AS NMONTH
from TRANSSACTION t join CREDIT_CARD cc on t.Card_code=cc.Card_code
where datepart(day,t.trans_time_date) between 21 and 31 and
@Card_code=CONVERT(int,cc.Card_code )and
@Months=datepart(MONTH,t.Trans_time_date) and
@Charge=t.Charge
open CRS
FETCH NEXT FROM CRS
INTO @Card_code,@Months,@Charge
WHILE @@FETCH_STATUS=0
BEGIN
print @Card_code
print @Months
print @Charge
FETCH NEXT FROM CRS
INTO @Card_code,@Months,@Charge
END
CLOSE CRS DEALLOCATE CRS END
EXEC PRC1 @Card_code,Months,@Charge
当我执行它时,发生以下错误:必须声明标量变量"@Card_code"。
您没有在 exec
语句之后声明要传入的变量。执行存储过程时,需要使用文本值或变量传入存储过程定义中所需的参数。存储的 proc 定义有 3 个参数:
-
@Card_code bigint
-
@Months dateTIME OUT
-
@Charge decimal(10,2)
执行存储的过程时,需要在 exec 语句后提供请求的参数。由于参数 3 是输入和输出参数,并且将返回给调用方,因此需要传入声明的变量来存储结果。对于参数 1 和 3(它们只是输入参数),您可以为存储的过程提供常量值或您声明的用于存储值的变量。
DECLARE @myCardCode BIGINT = 999;
DECLARE @monthsReturned DATETIME = getDate();
EXEC PRC1 @myCardCode, @monthsReturned, 100.00 --parameter 1 passed in as variable, parameter 2 is a variable to store the output result, parameter 3 is a constant value