in a procedure update is failing due to checking of from_date and to_date

See more:

SQL-Server

hi,

for my banking application i have a tables called SCHEME and Scheme_valid

initially i will insert the scheme_type that must be entered into the both tables scheme and scheme_valid

my requirement is if particular scheme_id is already in scheme_valid table with the input of different valid_fromdate,valid_todate then new row is inserting.

but here my problem is while updating interst,penal_interst etc in Scheme_valid table with the existed valid_fromdate and valid_todate the new row is inserting instead of updating that row.ie, valid_fromdate , valid_todate is same for that row which we want to update the values ,


from page i will insert only the From_date only , then To_date is stored as NULL in the table.

here i want to check From_date between From_date and To_date.

USE [CSP]
GO
/****** Object:  StoredProcedure [dbo].[sp_CSP_SaveScheme]    Script Date: 10/18/2012 12:54:10 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_CSP_SaveScheme]
@ID int,
@Deposit_Type tinyint,
@Scheme_Name nvarchar(150),
@Mode_Operation tinyint=null,
@Period_Type tinyint=null,
@Period_Value int=null,
@Type_Interest tinyint=null,
@Interest_Rate float=null,
@PNL_Interest float=null,
@Valid_From_Date date=null,
@Valid_To_Date date=null,
@From_Date Date = null,
@User_Id int=null,
@IP_Address nvarchar(15)=null,
@Date_Time datetime=null,
@To_Date date=null,
@LockIn_Period int=null,
@Group_Id int
AS
BEGIN
DECLARE @L_Count INT=0 
DECLARE @R_Count INT=0
Declare @L_Return_Val Int 
Declare @L_Group_Id Int = NULL
Declare @L_Transaction_Type int
Declare @L_Master_Group_Id int
DECLARE @S_Id Int
DECLARE @SV_Id Int
DECLARE @X_Valid int
DECLARE @Valid_Scheme INT
DECLARE @Scheme_Name_Valid nvarchar(15) =Null

SET @L_Count = (Select Count(*) From scheme_valid Where Scheme_ID=(select id from scheme where Scheme_Name=@Scheme_Name));

if @L_Count>0
BEGIN
BEGIN TRANSACTION


 
--IF(@X_Valid >0)
--BEGIN
--UPDATE scheme_valid SET To_Date = DATEADD(DAY, DATEDIFF(DAY, 0, @From_Date), -1) WHERE From_Date = To_Date AND From_Date < @From_Date AND Scheme_ID=@ID  --AND Scheme_Name = @Scheme_Name
--END

IF @@ERROR <> 0
BEGIN
	ROLLBACK
	RETURN
END
 
 
 BEGIN
 declare @L_id int
	--Set @L_Master_Group_Id=(select [Group_Id] from Scheme where Scheme_Name LIKE @Scheme_Name);
	--select [Group_Id]=@L_Master_Group_Id, [ID]=@L_id from Scheme where Scheme_Name LIKE @Scheme_Name
	SET @L_Master_Group_Id = (select [Group_Id] from Scheme where Scheme_Name LIKE @Scheme_Name)
	SET @L_id = (select [ID] from Scheme where Scheme_Name LIKE @Scheme_Name)
	--INSERT INTO Scheme(Deposit_Type,Scheme_Name,Mode_Operation,Period_Type,Period_Value,Type_Interest,Interest_Rate,
	--PNL_Interest,Valid_From_Date,Valid_To_Date,[User_Id],IP_Address,Date_Time,From_Date,To_Date,LockIn_Period,Group_Id )
	--VALUES(@Deposit_Type,@Scheme_Name,@Mode_Operation,@Period_Type,@Period_Value,@Type_Interest,@Interest_Rate,        
	--@PNL_Interest,@Valid_From_Date,@Valid_To_Date,@User_Id,@IP_Address,GETDATE(),@From_Date,@From_Date,@LockIn_Period,@L_Master_Group_Id)
	 --SET @S_Id = SCOPE_IDENTITY()
  
  
  if @ID is not null
    begin
	  SET @X_Valid =(SELECT COUNT(*) FROM scheme_valid 
					WHERE (@From_Date between From_Date and To_Date 
					   or  @To_Date between From_Date and To_Date
					   or  From_Date between @From_Date and @To_Date
					   or  To_Date between @From_Date and @To_Date
						)
					   and Scheme_ID=@L_id)
					   
		if 	@X_Valid<=0 OR @X_Valid IS NULL
		
		begin		   
			INSERT INTO scheme_valid(Scheme_ID,Type_Interest,Interest_Rate,PNL_Interest,Valid_From_Date,Valid_To_Date,[User_Id],
			IP_Address,Date_Time,From_Date,To_Date,LockIn_Period)
			VALUES(@L_id,@Type_Interest,@Interest_Rate,@PNL_Interest,@Valid_From_Date,@Valid_To_Date,@User_Id,@IP_Address,GETDATE(),
			@From_Date,@To_Date,@LockIn_Period)
			 
		set @Scheme_Name_Valid = 'Available'
               
		end
		else
		begin
			--error message
			set @Scheme_Name_Valid = 'UnAvailable'
		end
    end
 else
	begin
		SET @X_Valid =(SELECT COUNT(*) FROM scheme_valid 
					WHERE (@From_Date between From_Date and To_Date 
					   or  @To_Date between From_Date and To_Date
					   or  From_Date between @From_Date and @To_Date
					   or  To_Date between @From_Date and @To_Date
						)
					   and Scheme_ID=@L_id and ID!=@ID)
		
		if 	@X_Valid<=0 OR @X_Valid IS NULL
		
		begin	
		   --Update statement	   
			UPDATE scheme_valid SET Scheme_ID=@L_id
			                       ,Type_Interest=@Type_Interest 
			                       ,Interest_Rate=@Interest_Rate 
			                       ,PNL_Interest=@PNL_Interest 
			                       ,Valid_From_Date=@Valid_From_Date 
			                       ,Valid_To_Date=@Valid_To_Date 
			                       ,[User_Id]=@User_Id 
			                       ,IP_Address=@IP_Address 
			                       ,Date_Time=GETDATE()
			                       ,From_Date=@From_Date 
			                       ,To_Date=@To_Date 
			                       ,LockIn_Period=@LockIn_Period 
			                 WHERE ID=@ID 
			                       
		
			set @Scheme_Name_Valid = 'Updated'
                
		end
		else
		begin
			--error message
			set @Scheme_Name_Valid = 'NotUpdated'
			SELECT @Scheme_Name_Valid as  Scheme_Name_Valid
			return
		end
	end	
IF @@ERROR <> 0
BEGIN
	ROLLBACK
	RETURN
END
COMMIT

END
END
ELSE

BEGIN
SET @R_Count=(Select Count(*) From Scheme  Where  Scheme_Name=@Scheme_Name);
IF @R_Count>0
BEGIN
SET @Scheme_Name_Valid='Unavailable' 
SELECT @Scheme_Name_Valid as  Scheme_Name_Valid
	return     
END
ELSE 
BEGIN

begin try
    
    Set @L_Transaction_Type=(select [Transaction_Type] from [Group_Header] where [ID]=@Group_Id);
	Set @L_Master_Group_Id=(select [Master_Group_Id] from [Group_Header] where [ID]=@Group_Id);
	--Install records into group header table										 
	    Execute [sp_CSP_Save_Group_Header]     @Scheme_Name,
											   @Group_Id,
											   '',
											   @User_Id,
											   @IP_Address,
											   @L_Transaction_Type,
											   @L_Master_Group_Id,
											   @L_Return_Val OUTPUT,
											   @L_Group_Id OUTPUT;
    IF @L_Return_Val>0
	BEGIN
		SET @Scheme_Name_Valid='Unavailable' 
		SELECT @Scheme_Name_Valid as  Scheme_Name_Valid
		RETURN     
	END
    
	INSERT INTO Scheme(Deposit_Type,Scheme_Name,Mode_Operation,Period_Type,Period_Value,Type_Interest,Interest_Rate,
	PNL_Interest,Valid_From_Date,Valid_To_Date,[User_Id],IP_Address,Date_Time,From_Date,To_Date,LockIn_Period,Group_Id )
	VALUES(@Deposit_Type,@Scheme_Name,@Mode_Operation,@Period_Type,@Period_Value,@Type_Interest,@Interest_Rate,        
	@PNL_Interest,@Valid_From_Date,@Valid_To_Date,@User_Id,@IP_Address,GETDATE(),@From_Date,@From_Date,@LockIn_Period,@L_Group_Id )
	
SET @SV_Id = SCOPE_IDENTITY()

    INSERT INTO scheme_valid(Scheme_ID,Type_Interest,Interest_Rate,PNL_Interest,Valid_From_Date,Valid_To_Date,[User_Id],
    IP_Address,Date_Time,From_Date,To_Date,LockIn_Period)
    VALUES(@SV_Id,@Type_Interest,@Interest_Rate,@PNL_Interest,@Valid_From_Date,@Valid_To_Date,@User_Id,@IP_Address,GETDATE(),
    @From_Date,@To_Date,@LockIn_Period) 	
	
	
	
	SET @Scheme_Name_Valid='Available' 

end try
begin catch

    ROLLBACK;
	DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    DECLARE @ErrorNumber INT;

    SELECT 
        @ErrorMessage = ERROR_MESSAGE(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorNumber = ERROR_NUMBER();

    -- Use RAISERROR inside the CATCH block to return error
    -- information about the original error that caused
    -- execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage,
			   @ErrorSeverity, -- Severity.
               @ErrorState, -- State.
               @ErrorNumber --Error number.
               );
               
               SELECT @Scheme_Name_Valid as  Scheme_Name_Valid
end catch

END



END

SELECT @Scheme_Name_Valid as  Scheme_Name_Valid

END