Click here to Skip to main content
15,506,552 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi there,

I am developing an application where i would like to write a log data that has been requested.

Currently i load student data based the student ids i have passed into my procedure.

I then attempt to load sub data for the student based on the request e.g Skills, Hobbies, Languages, Grades, etc etc. These are passed in my stored proc as individual collections.

After everything has been loaded for each student I have to log their student id and the corresponding static sub data identifier regarding the sub data e.g Skills has an id of 45, Hobbies has an id of 46 etc etc. The static sub identifier is the same for all students.

The table can have multiple records for that student(based on whats requested and providing it exists), the audit table looks like this:

Table Audit_table
- Id
- Student_Id
- StudentBioId


I would like some advice on the code i am trying to write. I am passing in two varrays, firstly the student identifiers and secondly identifiers relating to the sub data that has been requested for that student.

What is the convention for looping through multiple collections and inserting them appropriately against that student?

Currently i can only get one of them to work which is the StudentId varray:

SQL
CREATE OR REPLACE TYPE studentIDs IS VARRAY (256) OF NUMBER;
CREATE OR REPLACE TYPE studentBio IS VARRAY (256) OF NUMBER;

BEGIN
   FORALL i IN 1..studentIDs.COUNT
      INSERT INTO audit_table(Id, Student_id, StudentBioId) 
      VALUES  (-1, studentIDS(i), null);
END;


I would like to populate the StudentBioId for that student as well but i am unsure of how to do this appropriately for each student.

Advice/Examples would be much appreciated.

Kind Regards,
Posted
Comments
RossMW 21-Jun-15 23:16pm    
If I understand correctly you are wanting to loop thru studentbiod array for each studentid. If this is the case then you can have a studentBiod loop inside the studentid loop.

1 solution

SQL
--VARIABLE DECLARATION
DECLARE @STUDENTIDS VARCHAR(500)
DECLARE @STUDENT_IDCOUNT INT
DECLARE @STUDLOOPCOUNT INT
DECLARE @S_ID INT
DECLARE @S_BIO_ID VARCHAR(500) 

--EG
--SELECT @STUDENTIDS = '1,2,3,4,5,6'

IF (ISNULL(@STUDENTIDS, '') <> '' )
BEGIN
--TEMP TABLE
DECLARE @STUDTEMP TABLE
(
	ID INT IDENTITY(1,1) NOT NULL,
	STUDENT_ID VARCHAR(20),
	STUDENTBIO_ID VARCHAR(20)
) 
--INSERT #TEMP STATEMENTS	
INSERT INTO @STUDTEMP SELECT * FROM DBO.SPLIT(@STUDENTIDS, ',')
 
--SELECT COUNT
SELECT @STUDENT_IDCOUNT = COUNT(STUDENT_ID) FROM @STUDTEMP
 
 
--INSERT MULTIPLE RECORDS
IF(@STUDENT_IDCOUNT > 0)
BEGIN
	SET @STUDLOOPCOUNT = 1;
	WHILE (@STUDLOOPCOUNT <= @STUDENT_IDCOUNT)
	BEGIN
		
		--SELECT YOUR  CORRESPONDING STUDENT VALUES
		SELECT @S_ID     = STUDENT_ID FROM @STUDTEMP WHERE ID = @STUDLOOPCOUNT
		SELECT @S_BIO_ID = STUDENTBIO_ID FROM @STUDTEMP WHERE ID = @STUDLOOPCOUNT

		-- INSERTING VALUES INTO AUDIT_TABLE TABLE
		INSERT INTO AUDIT_TABLE 
		(	
			STUDENT_ID,
			STUDENTBIOID
		)

		VALUES 
		(	
			@S_ID,
			@S_BIO_ID
				 
		)
		SET  @STUDLOOPCOUNT = @STUDLOOPCOUNT + 1
	END	
END	



Please try above code for spilt fuction follow the given link.

http://www.aspdotnet-suresh.com/2013/07/sql-server-split-function-example-in.html[^]
 
Share this answer
 
Comments
Eagle32 23-Jun-15 11:55am    
I appreciate your assistance. Would this work in PL/SQL as I am using PL/SQL developer?
Eagle32 23-Jun-15 14:52pm    
Also in addition to my previous question, how can i adapt this code so that i only insert the studentBioID that belongs to the student? i.e if that student doesn't have a studentBioID then we do not populate it for that student.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900