Save checkedlistbox items in SQL table using stored procedure

See more:

C#

SQL

I have to save the checkedlistbox items into sql table along with some other textbox values using same stored procedure where as textbox values stored in different table and checkedlistbox values stored in different table ..


i want to save the "@SampleParameter" variable into a table called SampleParamDetails which has the collection of checkedlistbox items in array format ..

remaining parameters
@SampleId,
@SampleDept,
@SampleDate,
@SampleDeptFor,
@SDept ,
@CreatedBy

i have to save in another table called SampleReference

both action have to be achieved using single stored procedure..
how can i achieve that ?

What I have tried:

USE [LabProjectDb]
GO
/****** Object:  StoredProcedure [dbo].[SampleMaster]    Script Date: 27-06-2022 11.47.19 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
		ALTER PROCEDURE [dbo].[SampleMaster]
		
		

			@QueryType nvarchar(25),
			
			@SampleId nvarchar(15),
			@SampleDept nvarchar(15),
			@SampleDate nvarchar(15),
			@SampleDeptFor nvarchar(20),
			--@SampleParameter  NVARCHAR(200),
			@SDept NVARCHAR(10),
			@CreatedBy nvarchar(50),
			@SQLReturn nvarchar(50) output,	 
			@SampleParameter AS dbo.MyIDList2 READONLY
		AS
		BEGIN	
			SET NOCOUNT ON;
			 
			Begin Transaction SampleReference
			Declare @rcount int
			DECLARE @sep char(1);
			set @sep = ','
				

			--DECLARE @myTableVariable TABLE (id INT IDENTITY(1,1) PRIMARY KEY, name varchar(max))
			set @rcount=0
			if @QueryType='Insert'
				Begin
					 --if not exists(Select SampleId From SampleReference where SampleId=@SampleId)
						--Begin
							if @SDept='CM' 
								BEGIN
									
									Insert Into SampleReference(SampleId,SampleDate,Sample_CM,Sample_MB,SampleForCM,SampleForMB,CreatedBy,CreatedDateTime)
									Values(@SampleId,@SampleDate,@SampleDept,' ',@SampleDeptFor,' ',@CreatedBy,GETDATE())									
									
									if @@ROWCOUNT>0
										BEGIN
											DECLARE @INDEXVAR int  
											DECLARE @TOTALCOUNT int  
											DECLARE @CURRPARAMETER nvarchar (50) 
										-- Declare your array table variable  
											DECLARE @MYARRAY table (TEMPCOL nvarchar(50), ARRAYINDEX int identity(1,1) )
										-- Add values to your array table, these are the values which you need to look for in your database  
											INSERT INTO @MYARRAY (TEMPCOL)  VALUES  ('Parameter1'),('Parameter2'),('Paramter3')
											SET @INDEXVAR = 0 
											SELECT @TOTALCOUNT= COUNT(*) FROM @MYARRAY  							 
											WHILE @INDEXVAR <= @TOTALCOUNT 
													BEGIN
													-- Get value of current indexed product ID from array table  
														SELECT @CURRPARAMETER = TEMPCOL from @MYARRAY where ARRAYINDEX = @INDEXVAR
														Insert Into SampleReference_CM 
														(SampleId,SampleDate,SampleId_CM,SDepartment,SParameter,SampleCreatedFor,CreatedBy,CreatedDateTime)Values
														(@SampleId,@SampleDate,' ',@SampleDept,@CURRPARAMETER,@SampleDeptFor,@CreatedBy,GETDATE())
											END
											

										END
										if @@ROWCOUNT>0 set @rcount=1
								END
							if @SDept='MB'
									BEGIN
										Insert Into SampleReference (SampleId,SampleDate,Sample_CM,Sample_MB,SampleForCM,SampleForMB,CreatedBy,CreatedDateTime)
										Values(@SampleId,@SampleDate,@SampleDept,' ',@SampleDeptFor,' ',@CreatedBy,GETDATE())
										if @@ROWCOUNT>0
											Insert Into SampleReference_MB (SampleId,SampleDate,SampleId_MB,SDepartment,SParameter,SampleCreatedFor,CreatedBy,CreatedDateTime)
										Values(@SampleId,@SampleDate,' ',@SampleDept,(SELECT ID FROM @SampleParameter),@SampleDeptFor,@CreatedBy,GETDATE())
										if @@ROWCOUNT>0
											set @rcount=1
									END
							
							--End
						Else
							set @rcount=-1	
					End
					
			
			if @rcount>0
			Begin
				commit transaction SampleReference
				set @SQLReturn='Success'
			End	
			else if @rcount=-1
			Begin
				commit transaction SampleReference
				set @SQLReturn='Already'
			End	
			Else
			Begin
				rollback transaction SampleReference
				set @SQLReturn='Failure'
			End	   
		END