How do I maintain a variable with multiple values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

How do I maintain a variable with multiple values

Author

Topic

kpshah
Starting Member

2 Posts

Posted - 2008-09-15 : 15:11:38

I have created a SP with two parameters.
@CourseId int,
@GradeLevelId int

Here, CourseId will have one value but GradeLevelId could have multiple values means a course can have multiple grades.

Now I need to update 'GradeLevel' table
like
update GradeLevel
set GradeLevelId = @GradeLevelId
where CourseId = @CourseId

But this logic is appropriate only when there is only one single value in @GradeLevelId variable.

Can some one tell me how to work with a varible which has multiple values and store that each value in the table.

For your idea, varables can have these kind of data:

@CourseId = 2,
@GradeLevelId = 4,5,6,7

I need to store GradeLEvelId (4,5,6,7) for CourseId (2) into my GradeLevel table. So there will be 4 rows for CourseId '2' with different GradeLevelId (4,5,6,7) in GradeLevel table.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 15:19:36

what you need is insert rather than update. use it like below

INSERT INTO YourTable(CourceId,Level)
SELECT @CourseId,t.Val
FROM dbo.ParseValues(@GradeLevelId)t

ParseValues can be found at:-

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104485

kpshah
Starting Member

2 Posts

Posted - 2008-09-15 : 15:29:38

Thanks visakh16 for your reply.

I am using this stored procedure for my 'Edit A Course' form. So suppose, if someone clicks on 'Math' course for Edit, then it will redirect to the edit page with the values for a selected course.

In that form , there is a list of Grades(checkboxes) for a course. Here he can change his grade 1 to 2 , So I have to use update query to change my GradeLevel data 1 to 2 for 'Math' course for that user.

I hope you understand what I mean.

Any ideas!?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-16 : 00:09:22

quote:
Originally posted by kpshah

Thanks visakh16 for your reply.

I am using this stored procedure for my 'Edit A Course' form. So suppose, if someone clicks on 'Math' course for Edit, then it will redirect to the edit page with the values for a selected course.

In that form , there is a list of Grades(checkboxes) for a course. Here he can change his grade 1 to 2 , So I have to use update query to change my GradeLevel data 1 to 2 for 'Math' course for that user.

I hope you understand what I mean.

Any ideas!?

then how will your GradeLevelId contain comma seperated list?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources