Please refer below article link-

Insert record and Update existing record if record exists using Stored Procedure in SQL Server

i am passing transaction values and Id's to SP in a string separated by comma. Used a split string function to split values and insert to table

  @shpVals varchar(max),@paramId varchar(max)

eg: shpVals ="3.5,1.2,3.98,0.23,5"

paramId="1025,1026,28,45,74"

i am inserting them as

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

my concern is updating the shpVals and paramId

i cannot update them as set Value=@ShpVals as ShpVals and paramId contain multiple values separated by comma. 

Hi bkc,

Please refer below sample.

Use while loop for Updating multiple Values.

Change your query according to your table structure and condition.

SQL

CREATE PROCEDURE GetUpdatedCustomers
	@Country VARCHAR(max),
	@CustomerName VARCHAR(max),
	@CustomerId VARCHAR(max)
AS
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
GO

SELECT * FROM CustomerTest11
GO
EXEC GetUpdatedCustomers 'John Hammond,Mudassar Khan','United States,India','1,2'
GO
SELECT * FROM CustomerTest11


SELECT * FROM CustomerTest11
GO
EXEC GetUpdatedCustomers 'Suzanne Mathews,Robert Schidner','France,Russia','3,4'
GO
SELECT * FROM CustomerTest1

Screenshot

pandeyism says:

 SELECT @CountryName = Item FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) RowNumber,Item FROM dbo.SplitString(@Country, ',') t) y WHERE RowNumber = @Counter

 could you pls tell me what are t and y in the query

Hi bkc,

bkc says:

 could you pls tell me what are t and y in the query

 t and y are alias.

Please refer below link:-

https://docs.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-2017