store procedure

Question

store procedure

raghunandansharma12

SSC Rookie

Points: 48
More actions
February 4, 2008 at 12:18 am

#181622

Plz Help me
I have a store procedure
Problem is that i have three table class,classSubject_Relation and Subject........
class table consist column cls_id(pk),cls_name
class_subject table cloumn cls_id(fk),sub_id(fk)
subject table column sub_id(pk),sub_name
How can i store differnt subject with one classId where cls_id is Auto-gentrated(identity)
i want data is stored in database like
in class table
cls_id cls_name
1 MCA
In relational table
cls_id(fk) sub_id(fk)
1 1
1 2
1 3
Here @sub_id contains string such as @sub_id=('1,2,3,)
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[insertvalues]
@cls_name nvarchar(50),
@cls_strength int,
@grp_id int,
@sub_id nvarchar(50)
AS
BEGIN
DECLARE @pos int,
@nextpos int ,
@valuelen int
SELECT @pos = 0, @nextpos = 1
WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @sub_id, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@sub_id) + 1
END - @pos - 1
--insert into class_subject(cls_id,sub_id) values (@@identity,@sub_id)
INSERT into class_subject(cls_id,sub_id)
VALUES(@@identity,(convert(int, substring(@sub_id, @pos + 1, @valuelen))))
SELECT @pos = @nextpos
select @@identity
END
RETURN
insert into class(cls_name,cls_strength,grp_id) values(@cls_name,@cls_strength,@grp_id)
end
Raghu

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

I'm not sure I fully understand your question. What does the code you've written do that it's no supposed to do, or not do what it's supposed to do?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Ian Yates SSCoach Points: 19738 More actions · Answer 2

For starters, in your classes_subjects joining table you do not want either of the columns to be created as identity columns. Period!

Next, please oh please format your code using the {code} {/code} (replace { with [ to use) - it makes it so much easier to read.

Finally, you want to do something like

declare @classID int

insert into class(name, x, y, z, etc) values(@className, ......)

set @classID = scope_identity() --this is better than @@IDENTITY. Look it up for why.

--then have your looping to get the subject IDs (look up passing an array to a stored procedure for nice ways of doing this)

--Your insert statement should be along the lines of

insert into classesSubjects(classID, subjectID)

values(@classID, @subjectID)

If you have referential integrity constraints on your tables (ie you have "joins" between them) then you need to have the class present before you can have class+subject entries.

Note that I haven't kept your table names because the ones I used above were easier to type 😀

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 3

Dear raghunandansharma12 ,

Here is the solution of the problem that you stated :

How can i store differnt subject with one classId where cls_id is Auto-gentrated(identity)

What you need to do is modify the below code

insert into class(cls_name,cls_strength,grp_id) values(@cls_name,@cls_strength,@grp_id)

You should insert if the @cls_name is not existing in the table else it should update. This will ensure that you will not have multiple rows for one class and also solve the problem your stated.

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]

Mohammad Mazharuddin Ehsan SSCarpal Tunnel Points: 4552 More actions · Answer 4

Also, remove the

RETURN

What is it doing here.

-----------------------------------------------------------[font=Arial Black]Time Is Money[/font][font=Arial Narrow]Calculating the Number of Business Hours Passed since a Point of Time[/url][/font][font=Arial Narrow]Calculating the Number of Business Hours Passed Between Two Points of Time[/font]