存储过程高手请进

[yyq136]

Microsoft OLE DB Provider for ODBC Drivers 错误 '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]第 1 行: '10' 附近有语法错误。
请问这个错误是什么意思!

[yyq136]

ASP程序中的存储过程调用：
set cmd = Server.CreateObject("ADODB.Command")
cmd.activeConnection = Conn
cmd.CommandType = 4
cmd.CommandText = "PBatch_Card"
cmd.Parameters.Append cmd.CreateParameter("SellCardNumber", 200, 1,15,iAmount)
cmd.Parameters.Append cmd.CreateParameter("Sale_Cust_Id", 200, 1,18,session("custid"))
cmd.Parameters.Append cmd.CreateParameter("Login_IP", 200, 1,15,Request.ServerVariables("REMOTE_ADDR"))
cmd.Parameters.Append cmd.CreateParameter("Card_Type", 200, 1,15,sCard_Type)
cmd.Parameters.Append cmd.CreateParameter("sell_money", 6, 1,8,sell_money)
cmd.Parameters.Append cmd.CreateParameter("operator_id", 200, 1,18,session("operatorid"))
cmd.Parameters.Append cmd.CreateParameter("FSPRINTID",200,4,20)
cmd.Parameters.Append cmd.CreateParameter("ERR_CODE", 3, 4)
cmd.execute()

[yyq136]

存储过程的源码：
CREATE PROCEDURE PBatch_Card
@SellCardNumber varchar(15),@SALE_CUST_ID varchar(18),@Login_IP varchar(15),
@CARD_TYPE varchar(15),@sell_money money,@operator_id varchar(18),
@FSPRINTID varchar(20) output,@ERR_CODE INT output
AS
DECLARE @mSellCardNumber INT,@mSTR varchar(8000)
DECLARE @mYY varchar(4),@mMM varchar(2),@mDD varchar(4),@mSEQ INT,@mGET_SEQ_DATE datetime
DECLARE @mSEQ_DATE datetime,@mSEQ_TYPE varchar(2),@mGET_SEQ varchar(20)
DECLARE @sPROVINCEID varchar(20),@sSTORAGEID varchar(20),@sQUANTITY INT

SET @mSellCardNumber=CONVERT(INT,@SellCardNumber)
SET @ERR_CODE=0
SET @mYY=REPLACE(RIGHT(STR(YEAR(GETDATE ( ) )),2),' ','0')
SET @mMM=REPLACE(RIGHT(STR(MONTH(GETDATE())),2),' ','0')
SET @mDD=REPLACE(RIGHT(STR(DAY(GETDATE())),2),' ','0')
SET @mGET_SEQ_DATE=CONVERT(VARCHAR,GETDATE(),111)

SELECT @mSEQ=SEQ,@mSEQ_DATE=SEQ_DATE,@mSEQ_TYPE =SEQ_TYPE FROM EC_SEQ
WHERE SEQ_NAME='CARD_EXCHG_SEQ'
IF @mSEQ_DATE IS NULL OR @mSEQ_DATE<>@mGET_SEQ_DATE
BEGIN
SET @mSEQ=1
END
SET @mSEQ=@mSEQ+@mSellCardNumber
SELECT TOP 1 @sPROVINCEID=PROVINCEID, @sSTORAGEID=STORAGEID FROM EC_CUST
WHERE CUST_ID= @SALE_CUST_ID

SELECT @sQUANTITY = fCardQuantity FROM tCustomOwnership
WHERE fCustomName= @SALE_CUST_ID and fCardTypeID = @CARD_TYPE
IF @sQUANTITY < @SellCardNumber
BEGIN
SET @ERR_CODE =1
RETURN
END
DECLARE @sSERIAL INT
SET @sSERIAL=(SELECT MAX(fCustomDaySerial) FROM tCardSaleRecord
WHERE datediff(day,fSaleTime,getDate())=0 and fSaleCustomName = @SALE_CUST_ID GROUP BY fSaleCustomName)
IF @sSERIAL IS NULL
BEGIN
SET @sSERIAL=1
END
DECLARE @printid VARCHAR(20)
SET @sSERIAL=@sSERIAL+@mSellCardNumber
SET @mGET_SEQ= @mSEQ_TYPE+@mYY+@mMM+@mDD+REPLACE(STR(@mSEQ,6),' ','0')
SET @printid=SUBSTRING(@mGET_SEQ, 3, 6)+RIGHT ('00' + CAST(@sPROVINCEID AS varchar(255)), 2) + RIGHT ('00000' + CAST(@sSTORAGEID AS varchar(255)), 5) + RIGHT ('0000' + CAST(@sSERIAL AS varchar(255)),4)

BEGIN TRANSACTION

SET @mSTR='INSERT INTO TCARDSALERECORD(fSaleShowID,fSalePrintID,fSaleCustomName,fSaleOperatorID,fCardTypeID,fSalePrice,fSaleTime,fSaleIP,fCustomDaySerial,fCardImportID,fCardAccount,fCardPassword)'
SET @mSTR=@mSTR+'SELECT TOP '+CAST(@mSellCardNumber AS VARCHAR)
SET @mSTR=@mSTR+' '+@mGET_SEQ+','+ @printid+','+@SALE_CUST_ID+','+@operator_id+','+@CARD_TYPE+','+convert(varchar, @sell_money)+','+cast(getdate() as varchar)+','+ @Login_IP+','+cast(@sSERIAL as varchar)+',fCardImportID,fCardAccount,fCardPassword '
SET @mSTR=@mSTR+' from tcardonsale where fCardTypeID ='+ @CARD_TYPE+' and fCardSaleFlag is null order by fCardImportID '
--print @mSTR
EXECUTE (@mSTR)
IF @@ERROR<>0 or @@rowcount<>@mSellCardNumber
BEGIN
SET @ERR_CODE =1
ROLLBACK TRANSACTION
END
SET @mSTR='delete tcardonsale where fcardid in('
SET @mSTR=@mSTR+'select top '+CAST(@mSellCardNumber AS VARCHAR)
SET @mSTR=@mSTR+' fcardid from tcardonsale where fCardTypeID ='+ @CARD_TYPE+' and fCardSaleFlag is null order by fCardImportID) '
EXECUTE (@mSTR)
IF @@ERROR<>0 or @@rowcount<>@mSellCardNumber
BEGIN
SET @ERR_CODE =2
ROLLBACK TRANSACTION
END
SET @FSPRINTID=@printid
UPDATE tCustomOwnership SET fCardQuantity = fCardQuantity - @mSellCardNumber
WHERE fCustomName= @SALE_CUST_ID and fCardTypeID = @CARD_TYPE
IF @@ERROR<>0
BEGIN
SET @ERR_CODE =3
ROLLBACK TRANSACTION
END
UPDATE EC_SEQ SET SEQ =@mSEQ,SEQ_DATE=@mGET_SEQ_DATE
WHERE SEQ_NAME='CARD_EXCHG_SEQ'
IF @@ERROR<>0
BEGIN
SET @ERR_CODE =4
ROLLBACK TRANSACTION
END
COMMIT TRANSACTION
GO

[yyq136]

调试好长时间了，老是不成功！请各位高手帮帮忙！在线急

[skyboy0720]

你这样调试！
先在查询分析器里人工输入参数，检测存储过程是否执行成功，是否得到你想要的结果！
如果通过，说明存储过程本身没问题，然后在ASP页面里，把传递的参数在执行存储过程前
打印出来，检查是否都有值，一步一步调试！！

[cheng17]

学到了一招调试方法

[cheng17]

你的存储过程参数是字符型
cmd.Parameters.Append cmd.CreateParameter("Login_IP", 200, 1,15,'Request.ServerVariables("REMOTE_ADDR")')

[luobinsen007]

这样试试看

cmd.CommandText = "{call PBatch_Card(?,?,?,?,?,?,?,?)}"

[yyq136]

luobinsen007(罗滨森)兄，cmd.CommandText = "{call PBatch_Card(?,?,?,?,?,?,?,?)}"
用你给我的方法调试没有提示错误啊。能进一步再帮我查查是哪出错了吗？

[mygia]

学习！

[hawk2004]

学！~