Problem with Output Parameter

[Syerston]

Could somone please have a look at the following piece of code and tell me what I am doing wrong.

I am trying to populate @ReturnId with @@identity and use the value to insert into the second table. But it keeps coming up with an overflow error.

CREATE PROCEDURE InsertClaimant
 
	@ProviderID		integer,
	@SchemeID		integer,
	@HBReference		char		(9),
	@ProtectedCase	bit,
 	@CreditDebit		char		(1),
	@StartDate		datetime,
	@EndDate		datetime,
	@Title			VarChar		(4),
	@Surname		VarChar		(16),
	@Forename		varchar		(12),
	@HBAmount		Money,
	@Initials		VarChar		(4),
	@PaymentPeriod	Integer,
	@PaymentWeek	Integer,
	@CDStartDate		DateTime,
	@CDEndDate		DateTime,
	@NoWeeks		Integer,
	@Rate			Money,
	@Total			Money,
	@PublicHB		Bit,
	@FlatNo		VarChar		(4),
	@HouseName		VarChar		(24),
	@HouseNo		VarChar		(4),
	@Address1		VarChar		(24),
	@Address2		VarChar		(24),
	@Address3		VarChar		(24),
	@Postcode		VarChar		(15),
	@ActionID		Integer,
	@ReturnID		Integer		Output				

AS
	DECLARE @InsertStartDate	DateTime
	SELECT @InsertStartDate = CONVERT(datetime,(CONVERT(varchar,@StartDate,103)))

	DECLARE @InsertEndDate	DateTime
	SELECT @InsertEndDate = CONVERT(datetime,(CONVERT(varchar,@EndDate,103)))


	INSERT INTO ClaimantDetails (ProviderID,SchemeID,HBReference,ProtectedCase,Title,Forename, StartDate, EndDate, Surname, HBAmount, Initials, PublicHB, FlatNumber, HouseName, HouseNumber, Address1, Address2, Address3, Postcode)
 	VALUES (@ProviderID,@SchemeID,@HBReference, @ProtectedCase, @Title, @Forename, @InsertStartDate, @InsertEndDate, @Surname, @HBAmount, @Initials, @PublicHB, @FlatNo, @HouseName, @HouseNo, @Address1, @Address2, @Address3, @Postcode)
	SET @ReturnID = @@Identity

		If @CreditDebit = 'c' or @CreditDebit = 'd' 
			Begin
				DECLARE @InsertCDStartDate	DateTime
				SELECT @InsertCDStartDate = CONVERT(datetime,(CONVERT(varchar,@CDStartDate,103)))	

				DECLARE @InsertCDEndDate	DateTime
				SELECT @InsertCDEndDate = CONVERT(datetime,(CONVERT(varchar,@CDEndDate,103)))	

				INSERT INTO CreditDebit (HBRef, SchemeID, ProviderID, WeekNo, PeriodNo, CreditDebit, StartDate, EndDate, NoWeeks, Rate, Total)
				VALUES (@HBReference, @SchemeID, @ProviderID, @PaymentWeek, @PaymentPeriod, @CreditDebit, @InsertCDStartDate, @InsertCDEndDate, @NoWeeks, @Rate, @Total, @ReturnID)
			End

	UPDATE ActionList
		SET Processed = '1'
		WHERE SerialID = @ActionID
GO

John

 

[DataJanitor]

It could be because the return type of @@Identity is numeric rather than Integer.

But, there could be other problems. . .

DJ

 

[JamesLean]

Check the data type of the identity column in the ClaimantDetails table. If it is bigint rather than int you will need to declare your local variable as the same. --James

 

[Syerston]

No it's an int John