Update record by spiting string using While loop in SQL Server

i have made changes to my SP.

values are getting inserted but not updating

	ALTER PROCEDURE [dbo].[SP_SaveShpValues]
		@shpVals varchar(max),
		@user varchar(30),
		@status int out, 
		@intAnalysisId int,
		@valwith int,
		@qdmt decimal(8,3),
		@qwmt decimal(8,3),
		@moisture decimal(8,3),
		@sDate varchar(50),
		@strAnal varchar(30),
		@vesId int,
		@paramId varchar(max),
		@grade int
	AS
	declare @varvaltime datetime
	declare @var_summary_id int
	declare @tcount int
	declare @counter int

BEGIN
	set @varvaltime= sysdatetime()
	SET @sDate=(SELECT REPLACE(@sDate,'"',''))
	set @tcount =Len(@shpVals)-len(replace(@shpVals,',',''))+1
	set @counter=1

if ( not exists( select Shp_summary_Id from TRAN_SHIPMENT_SUMMARY where SailDate = @sDate and Vessel_Id= @vesId ))
BEGIN
Insert into TRAN_SHIPMENT_SUMMARY (Analysis_Id,Vessel_Id,SailDate,Moist,Shp_QWmt,Shp_Qdmt) values (@intAnalysisId,@vesId,@sDate,@moisture,@qwmt,@qdmt)
set @var_summary_id= (select Shp_summary_Id from TRAN_SHIPMENT_SUMMARY where SailDate = @sDate and Vessel_Id= @vesId)
END
		
--if(not exists(select Shp_summary_Id from TRAN_SHIPMENT_VALUES where Shp_summary_Id=@var_summary_id and SailDate = @sDate and Vessel_Id= @vesId and Valuewith=@valwith and Analysis_Id=@intAnalysisId))
		BEGIN
		
		insert into TRAN_SHIPMENT_VALUES (Value,Shp_Parameter_Id,SailDate,Vessel_Id,Analysis_Id,ValueTime ,Valuewith,Grade,Last_Updated_By,IsActive,IsDeleted,Shp_summary_Id)
		select Value.Value, Shp_Parameter_Id.Shp_Parameter_Id,@sDate SailDate , @vesId Vessel_Id ,@intAnalysisId Analysis_Id ,@varvaltime ValueTime,@valwith Valuewith,@grade Grade,@user Last_Updated_By,IsActive=1,IsDeleted=0,( select Shp_summary_Id from TRAN_SHIPMENT_SUMMARY where SailDate = @sDate and Vessel_Id= @vesId) Shp_summary_Id
		from (SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT 0)) RowNo,TRY_CONVERT(decimal(8,3),Item) Value FROM dbo.SplitString(@shpVals, ',')  ) Value,
			 (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) RowNo,TRY_CONVERT(int,Item) Shp_Parameter_Id FROM dbo.SplitString( @paramId, ',') ) Shp_Parameter_Id 
		where  Shp_Parameter_Id.RowNo=Value.RowNo			
		
		END	
 if (exists(select Shp_Value_Id  FROM TRAN_SHIPMENT_VALUES where SailDate = @sDate and Vessel_Id= @vesId and Valuewith=@valwith and Analysis_Id=@intAnalysisId and Shp_summary_Id=@var_summary_id))
	begin
	
	while (@counter<=@tcount)
	BEGIN
		declare @var_shpvals varchar(max), @var_paramid varchar(max),@var_valueID varchar(100)
		select @var_shpvals = Value FROM (SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT 0)) RowNo,TRY_CONVERT(decimal(8,3),Item) Value 
											FROM dbo.SplitString(@shpVals, ',')  ) Value where RowNo=@counter
		
		select @var_paramid = Shp_Parameter_Id FROM 
												(SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) RowNo,TRY_CONVERT(int,Item) Shp_Parameter_Id
													FROM dbo.SplitString( @paramId, ',') ) Shp_Parameter_Id where RowNo=@counter
	
		select @var_valueID = (select Shp_Value_Id  FROM TRAN_SHIPMENT_VALUES where SailDate = @sDate and Vessel_Id= @vesId and Valuewith=@valwith and Analysis_Id=@intAnalysisId and Shp_summary_Id=@var_summary_id)
												
			UPDATE TRAN_SHIPMENT_VALUES
			
			SET Value=@var_shpvals

			WHERE  Shp_Value_Id =@var_valueID and Shp_summary_Id= @var_paramid

	SET @counter=@counter+1
	Continue
	END
	END
END

Hi bkc,

Please refer below query, According to query change your query.

SQL

CREATE PROCEDURE GetUpdatedCustomers
	@Country VARCHAR(max),
	@CustomerName VARCHAR(max),
	@CustomerId VARCHAR(max)
AS
BEGIN	
	IF NOT EXISTS(SELECT CustomerId FROM CustomerTest11 WHERE CustomerId=@CustomerId)
	BEGIN
		INSERT INTO CustomerTest11(Name,Country) VALUES(@CustomerName,@Country)
	END	
	ELSE
	BEGIN	
		DECLARE @TotalCount INT
		SET @TotalCount = LEN(@Country) - LEN(REPLACE(@Country, ',', '')) + 1
		DECLARE @Counter INT
		SET @Counter = 1
		
		WHILE (@Counter <= @TotalCount)
		BEGIN
			DECLARE @CountryName VARCHAR(50),@Name VARCHAR(50), @Id VARCHAR(50)
			SELECT @CountryName = Item FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) RowNumber,Item FROM dbo.SplitString(@Country, ',') t) y WHERE RowNumber = @Counter
			SELECT @Id = Item FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) RowNumber,Item FROM dbo.SplitString(@CustomerId, ',') t) y WHERE RowNumber = @Counter
			SELECT @Name = Item FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) RowNumber,Item FROM dbo.SplitString(@CustomerName, ',') t) y WHERE RowNumber = @Counter
			-- Update Statement.
			UPDATE CustomerTest11 
			SET Name = @Name,
				Country = @CountryName
			WHERE CustomerId = @Id	
				
			SET @Counter = @Counter + 1
			CONTINUE;
		END
	END
END