Multiple Inserts using SP
-
I am trying to insert multiple records into my DB via a Stored Procedure that I am working on now.
I have the necessary input parameters. Now All i need to do is to Retrieve a Set of USER_ID from my table and using those ids I will start to insert my record.
Here is my SP
CREATE PROCEDURE ATTENDENCE @YEAR int, @SEMESTER char(1),@DATE char(10), @CLASS_TOWER varchar(50), @CLASS_ROOM varchar(50), @CODE int, @ID varchar(9) OUTPUT AS
DECLARE @CLASS_ID as int
SELECT @CLASS_ID = CLASS_ID FROM CLASS WHERE CLASS_TOWER = @CLASS_TOWER AND CLASS_ROOM = @CLASS_ROOM
SELECT @ID =
CASE
WHEN DAY_1_CODE = @CODE AND DAY_1_CLASS = @CLASS_ID THEN USER_ID
WHEN DAY_2_CODE = @CODE AND DAY_2_CLASS = @CLASS_ID THEN USER_ID
WHEN DAY_3_CODE = @CODE AND DAY_3_CLASS = @CLASS_ID THEN USER_ID
WHEN DAY_4_CODE = @CODE AND DAY_4_CLASS = @CLASS_ID THEN USER_ID
WHEN DAY_5_CODE = @CODE AND DAY_5_CLASS = @CLASS_ID THEN USER_ID
END
FROM LESSON WHERE YEAR = @YEAR AND SEMESTER = @SEMESTER
I have tested and it seems that I am only getting 1 record back from my S.P which is the last record of my table. Im quite new to sql, so trying my best to learn.
How am I going to get a list of USER_ID from my SP.
Once I get my USER_ID : INSERT INTO ATTENDANCE VALUES (@DATE,@USER_ID)
I be doing a Insert Statement shown.
How am I going to do a multiple inserts into my attendance table with my list of USER_ID.
Could anyone care to help me.
-
An output parameter on a procedure will only allow you return 1 value, so if I'm reading this correctly you'll need to drop the output param (@ID) on the procedure.
insert into attendance
select @date, user_id
from lesson
where year = @year
and semester = @semester
and ((day_1_code = @code and day_1_class = @class_id)
or (day_2_code = @code and day_2_class = @class_id)
or (day_3_code = @code and day_3_class = @class_id)
or (day_4_code = @code and day_4_class = @class_id)
or (day_5_code = @code and day_5_class = @class_id))
-
Hi Raj,
I am not sure of the logic for User_ID. However, If you are using variables to insert into a table you can insert only one row.
Assuming that @ID refers to user ID you can write a procedure like this: Again, you may have to modify the procedure if the logic is not correct.
CREATE PROCEDURE ATTENDENCE @YEAR int, @SEMESTER char(1),@DATE char(10), @CLASS_TOWER varchar(50), @CLASS_ROOM varchar(50), @CODE int
AS
DECLARE @CLASS_ID as int
SELECT @CLASS_ID = CLASS_ID FROM CLASS WHERE CLASS_TOWER = @CLASS_TOWER AND CLASS_ROOM = @CLASS_ROOM
INSERT INTO ATTENDANCE
SELECT @DATE,
CASE
WHEN DAY_1_CODE = @CODE AND DAY_1_CLASS = @CLASS_ID THEN USER_ID
WHEN DAY_2_CODE = @CODE AND DAY_2_CLASS = @CLASS_ID THEN USER_ID
WHEN DAY_3_CODE = @CODE AND DAY_3_CLASS = @CLASS_ID THEN USER_ID
WHEN DAY_4_CODE = @CODE AND DAY_4_CLASS = @CLASS_ID THEN USER_ID
WHEN DAY_5_CODE = @CODE AND DAY_5_CLASS = @CLASS_ID THEN USER_ID
END
FROM LESSON WHERE YEAR = @YEAR AND SEMESTER = @SEMESTER
Select CASE
WHEN DAY_1_CODE = @CODE AND DAY_1_CLASS = @CLASS_ID THEN USER_ID
WHEN DAY_2_CODE = @CODE AND DAY_2_CLASS = @CLASS_ID THEN USER_ID
WHEN DAY_3_CODE = @CODE AND DAY_3_CLASS = @CLASS_ID THEN USER_ID
WHEN DAY_4_CODE = @CODE AND DAY_4_CLASS = @CLASS_ID THEN USER_ID
WHEN DAY_5_CODE = @CODE AND DAY_5_CLASS = @CLASS_ID THEN USER_ID
END
FROM LESSON WHERE YEAR = @YEAR AND SEMESTER = @SEMESTER
GO
Cheers,
Prithiviraj Kulasinghamhttp://preethiviraj.blogspot.com/
-
I just saw the reference of User_ID in the code.
The post before will work.
Cheers,
Prithiviraj Kulasinghamhttp://preethiviraj.blogspot.com/
-
Thanks for the code, may i know why are there 2 Select Case statements?
I have intented to add another value into my Attendance table, how can i add in inside the Insert statement you have given above..
Thanks
-
raj - are you sure preethviraj's solution is the logic you want to use ?!
Also, what do you mean by wanting to add "another value"...is that an additional filter that'll fetch more rows or inserting to another column in the "attendance" table ?!?!
**ASCII stupid question, get a stupid ANSI !!!** -
I figured that out on how to insert into another column in the attendance table.
But the problem is, the logic is correct, and it works but the S.P ends up inserting a NULL value..Which is not possible
WHEN DAY_1_CODE = @CODE AND DAY_1_CLASS = @CLASS_ID THEN USER_ID
WHEN DAY_2_CODE = @CODE AND DAY_2_CLASS = @CLASS_ID THEN USER_ID
WHEN DAY_3_CODE = @CODE AND DAY_3_CLASS = @CLASS_ID THEN USER_ID
WHEN DAY_4_CODE = @CODE AND DAY_4_CLASS = @CLASS_ID THEN USER_ID
WHEN DAY_5_CODE = @CODE AND DAY_5_CLASS = @CLASS_ID THEN USER_ID
Somce from this code it should only get the USER_ID if I have the correct CODE and CLASS_ID. But what if my DAY_(x)_CODE has a match and my DAY(x)_CLASS does not have a match, it still ends up inserting this record into my Attendance table - with a NULL as USER_ID.
-
The reason for the second select statement is we dont have an output parameter. when you have a parameter, it will return only one value. In multiple inserts, you may need to know the records affected.
Cheers,
Prithiviraj Kulasinghamhttp://preethiviraj.blogspot.com/
-
Yes i understood it.. I noticed that. Could you see my previous post on the problem im facing..
The sp does the insert, but it also inserts NULL value (when that CASE statement is not fufilled.
-
Do you want to insert a default value instead of NULL then add ELSE <DefaultValue> at the end of your case statement (before end)
If you want to limit the records where User_ID is NOT NULL, use the script provided by Joe.
Cheers,
Prithiviraj Kulasinghamhttp://preethiviraj.blogspot.com/
-
I am confused. Lets say for this statement
WHEN DAY_1_CODE = @CODE AND DAY_1_CLASS = @CLASS_ID THEN USER_ID
from the SP, will it insert a record if 1 Condition is satisfied or must 2 conditons be satisfied.
From what i see it needs both conditions to be satisfied. But from my problem for 1 record only i condition is statisfied but the other is not, but it ends up inside my Attendence table with a NULL userid
-
What do you want to insert when only one condition is satisfied?
If you dont want to insert that row, place what in the where clause.
Hope this is more clear
Cheers,
Prithiviraj Kulasinghamhttp://preethiviraj.blogspot.com/
-
Im abit not clear..."Place what in the Where clause" How would you do that.
-
Open BOL and type "WHERE clause, SELECT" in index tab.
_____________
Code for TallyGenerator -
Read the first comment by Joe Nakanishi
Cheers,
Prithiviraj Kulasinghamhttp://preethiviraj.blogspot.com/
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply