Click here to Skip to main content
15,886,199 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
Hello there!

Working with a server(game server) i got in trouble and i cant get a full fix.
My error is
SendNoticeMail : [Microsoft][ODBC SQL Server Driver][SQL Server]The formal parameter "@pSvrNo" was not declared as an OUTPUT parameter, but the actual parameter passed in requested output.(1)

The procedure looks like:
SQL
ALTER  PROCEDURE [dbo].[UspMailNoticeSend]
	 @pSender			NVARCHAR(20),
	 @pMailType			TINYINT,
	   @Preceiver			NVARCHAR(20),
	   @ptitle			NVARCHAR(40),
	 @pMain				NVARCHAR(200),
	 @pKeepTerm			TINYINT,
	 @pMoney			BIGINT,
	 @pItemNo			INT,
	 @pItemCnt			INT,
	 @pDur				INT,
	 @pMaxDur			INT,
	 @pIsBind			BIT,
	 @pUnBindCnt		TINYINT,
	   @PenchantStep		TINYINT,
	   @premainMin		INT,
	   @premainBase		INT,
	 @pValidMin			INT = 0,
	 @pSvrNo			SMALLINT = 0,		
	 @pItemSerial		BIGINT			OUTPUT,
	 @pMailNo			INT				OUTPUT, 
	 @pDate				DATETIME		OUTPUT,
	 @pRsMoney			BIGINT			OUTPUT
AS
	SET NOCOUNT ON;
	SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
	
	DECLARE   @amailCnt		INT
	DECLARE @aReceiverNo	INT
	DECLARE   @ASEnderMoney	BIGINT
	DECLARE @aEndDate		SMALLDATETIME
	DECLARE @aRead			TINYINT

	SELECT	@pMailNo = 0
		,	@pItemSerial = 0
		,	@pDate = GETDATE()
		,	@pRsMoney = 0
		,   @amailCnt = 0
		,	@aReceiverNo = 0
		,   @ASEnderMoney = 0
		,	@aRead = 0

	
	IF(@pMailType <> 1)
	BEGIN
		RETURN (1);
	END


	IF(@pValidMin > 0)
	BEGIN
		SET @aEndDate = DATEADD("n", @pValidMin, GETDATE())
	END
	ELSE
	BEGIN
		SET @aEndDate = dbo.UfnGetEndDate(GETDATE(), 0)
	END

	SELECT @aReceiverNo = mPcNo FROM dbo.TblPc WHERE mPcNm =   @Preceiver
	IF( @aReceiverNo = 0)
	BEGIN
		RETURN (2);			
	END

	SELECT @pMailNo = ISNULL(MAX(mMailNo), 0) + 1 FROM dbo.TblPcMail WHERE mPcNo = @aReceiverNo
	SELECT   @amailCnt = COUNT(mPcNo) FROM dbo.tblPcMail WHERE mPcNo = @aReceiverNo AND mMailType = @pMailType

	BEGIN TRY
		BEGIN TRAN
			IF( @pItemNo <> 0 )
			BEGIN
				EXEC dbo.UspGetItemSerial @pSvrNo, @pItemSerial OUTPUT; 	
				IF @pItemSerial <= 0  
					BEGIN
						RAISERROR ('Error raised in TRY block.', -- Message text.
									16	, -- Severity.
									2 -- State.  
									);	
					END

				INSERT INTO dbo.TblPcMailItem ( 
						 mRegDate, mSerialNo, mPcNo, mItemNo, mCnt,
						 mDur, mMaxDur, mIsBind, mUnBindCnt, mEnchantStep,
						 mRemainMin, mRemainBase, mEndDate )
				VALUES ( @pDate, @pItemSerial, @aReceiverNo, @pItemNo, @pItemCnt,
						 @pDur, @pMaxDur, @pIsBind, @pUnBindCnt,   @PenchantStep,
						   @premainMin,   @premainBase, @aEndDate)
			END

			IF(   @amailCnt >= 24 )
			BEGIN
				SET @aRead = 2
			END

			INSERT INTO dbo.TblPcMail ( 
					 mRegDate, mPcNo, mMailNo, mMailType, mSender, mTitle, mMain, mMoney, mItemSerial, mRead, mKeepTerm)
			VALUES ( @pDate, @aReceiverNo, @pMailNo, @pMailType, @pSender,   @ptitle, @pMain, @pMoney, @pItemSerial, @aRead, @pKeepTerm)
		
		COMMIT TRAN;
	END TRY
	
	BEGIN CATCH
		ROLLBACK TRAN;
		RETURN (1);
	END CATCH
	
	RETURN (0);


The procedure what does:
-send automatically items to a player with an reward via in game email

I tryed to execute it via MSSQL seems to run succefully but when server execute it thats the error i got.

Hope someone to provide me a fix.

Thanks for attention!

What I have tried:

I tryed to execute it via MSSQL seems to run succefully but when server execute it thats the error i got.
Posted
Updated 24-Jan-19 9:06am
v2

The code that calls the stored procedure has specified that the @pSvrNo is an OUTPUT parameter. But inside the stored procedure, it's not declared as OUTPUT.

You have two options to fix it: add OUTPUT to that parameter, as you have with the following four parameters; or change the calling code so that it doesn't declare that parameter as an OUTPUT parameter.

If you need help to change the calling code, then you'll need to show us the relevant part of that code.

Return Data from a Stored Procedure - SQL Server | Microsoft Docs[^]
 
Share this answer
 
I declared it as OUTPUT the error still not solved.

I get other error:

SendNoticeMail : [Microsoft][ODBC SQL Server Driver][SQL Server]Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.(1)
 
Share this answer
 
Comments
MadMyche 24-Jan-19 15:38pm    
Looks like the original error was resolved, and you found another.
The error message states very simply that it was defined as a DateTime, but was supposed to be an INT.
Member 14128924 24-Jan-19 15:40pm    
its smalldatetime defined and i have to define it as int? any suggestion query?
MadMyche 24-Jan-19 23:23pm    
That all depends on what the other stored procedure dbo.UspGetItemSerial needs, as that is the only place it is used
Member 14128924 25-Jan-19 3:15am    
Its not used in dbo.spGetItemSerial its ask now to convert in table dbo.TblPcMail
The column mRegDate from smalldatetime in INT...Any suggestion query to do it?Tryed few various sql converting querys returns to not a column name

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900