Post reply

How do I maintain a variable with multiple values like an array in Stored Proc?

  • September 15, 2008 at 1:02 pm

    #190834

    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.

  • September 15, 2008 at 3:45 pm

    #872061

    The simple datatypes (like int) cannot have multiple values at one time. If you want to do something like this you will need to:

    A) use a Varchar() string encode with the multiple values, then split the multiple values out in your SP (a tally table would probably be best for this), or

    B) Encode your list of values as multiple elements in an XML datatype, then use the .nodes() method to burst them out in your SProc, or

    C) use a Table Variable to hold your list of values and pass that in as a parameter (new in 2008!).

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 2 posts - 1 through 1 (of 1 total)

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