Hi everyone,
I got indulged into one situation where i have to insert values in SQL table using CSV (Comma Seperated Values) , now the situation became more critical where i need to insert values at a particular ID for a particular table.
i.e. i am having 2 tables namely:classroom, subject, syllabus
now i have data like 4,5,6 to be checked if present and if they are not present then they should be inserted for particular syllabus-id, now by getting the classroom-id for particular syllabus-id i have to insert subject eg. English, Maths for that particular classroom-id and syllabus-id .
I made the Stored Procedure for simple Insertion.......
But the problem was that after inserting the subject the parameter of subject became null OR '' so i stored it in one variable before inserting , and set the subject parameter again to the stored one.......
This whole process also could be done in asp.net with several checks and for loops , but i thought that if it would be done in asp.net then there would be much load to the server. Then i thought of making Stored Procedure for this whole process and here it is........
Stored Procedure......
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
Krunal N. Panchal
-- Create date: Monday,November ,22,2010
-- Description:
This Stored Procedure is for School Merge [For Insertion of Classroom And Subject By CSV]
-- =============================================
alter PROCEDURE [dbo].[Insert_Classroom_Subject]
--Parameters of Stored Procedure--
@classroom varchar(max),
@subject varchar(max),
@syllabusid varchar(max),
@classroomid varchar(max) =null out,
@subjectid varchar(max)=NULL out
AS
DECLARE @countclassroom int
DECLARE @strclassroom VARCHAR(max)
DECLARE @spotclassroom SMALLINT
DECLARE @storedsubject varchar(max)
SET @storedsubject =@subject
--------------for classroom insertion--------------
WHILE @classroom <> ''
BEGIN
SET @subject =@storedsubject
SET @spotclassroom = CHARINDEX(',', @classroom)
IF @spotclassroom>0
BEGIN
SET @strclassroom = CAST(LEFT(@classroom, @spotclassroom-1) AS varchar(max))
SET @classroom = RIGHT(@classroom, LEN(@classroom)-@spotclassroom)
END
ELSE
BEGIN
SET @strclassroom = CAST(@classroom AS varchar(max))
SET @classroom = ''
END
--Check Classroom if Present---
SELECT @countclassroom=count(1) FROM classroom
WHERE Classroom =@strclassroom
and SyllabusID =@syllabusid
----if Classroom not present then insert it----
IF @countclassroom =0
BEGIN
Insert into Classroom(Classroom ,SyllabusID )
values (@strclassroom ,@syllabusid )
set @classroomid=SCOPE_IDENTITY ();
END
Else
-----else get the id of the classroom for selected subject----
Begin
set @classroomid=(select classroomid from Classroom WHERE Classroom =@strclassroom
and SyllabusID =@syllabusid )
End
-------------------End of classroom insertion---------------------
-------------------For Subject Insertion-----------------------------
DECLARE @countsubject int
DECLARE @strsubject VARCHAR(max)
DECLARE @spotsubject SMALLINT
----------------For Storing the subject-------------
--SET @storedsubject =@subject
WHILE @subject <> ''
BEGIN
SET @spotsubject = CHARINDEX(',', @subject)
IF @spotsubject>0
BEGIN
SET @strsubject = CAST(LEFT(@subject, @spotsubject-1) AS varchar(max))
SET @subject = RIGHT(@subject, LEN(@subject)-@spotsubject)
END
ELSE
BEGIN
SET @strsubject = CAST(@subject AS varchar(max))
SET @subject = ''
END
-----------check if subject is present for particular classroom and syllabus
SELECT @countsubject=count(1) FROM subject
WHERE ClassroomID =@classroomid
and SyllabusID =@syllabusid
and Subject =@strsubject
-----count is 0 then insert the subject------
IF @countsubject =0
Begin
Insert into Subject(SyllabusID,ClassroomID,Subject)
values(@syllabusid ,@classroomid ,@strsubject )
set @subjectid =SCOPE_IDENTITY ();
End
End
---------------------Setting value of subject-----------
End
GO
Happy Coding.
Regards,
Krunal Panchal
