Array as procedure parameter

I have a shift definition table with the columns:

shift_id: shift's id

shift_name: shift's name

shift_number_of_day: shift's "position" on the day

initial_hour: shift's initial hour

final_hour: shift's final hour

The shift definition depends on the company: company A may have 2 shifts, and company B may have 3 shifts, for example.
I need to load a dimension table, dim_time, that should have a row for each hour of each day of a specific year. I would have

alternate_time_key ... shift_name ...
1/1/2006 01:00:00 GraveYard
1/1/2006 02:00:00 GraveYard

and so on, until it reaches the end of the year.

So in my procedure to load the dimension table, I would have something like

IF (@.alternateTimeKeyHour >= @.paramShift1InitialHour) AND (@.alternateTimeKeytHour <= @.paramShift1FinalHour)
BEGIN
SET @.shiftName = @.paramFirstShiftName;
SET @.shiftNumberOfDay = 1;
END
ELSE IF (@.alternateTimeKeyHour >= @.paramShift2InitialHour) AND (@.alternateTimeKeyHour <= @.paramShift2FinalHour)
BEGIN
SET @.shiftName = @.paramSecondShiftName;
SET @.shiftNumberOfDay = 2;
END
.
.
.

The problem is that I would have a variable number of shifts (variable number of parameters!)...
The only solution I could think was using an array, but as far as I could see it's not possible to pass
an array as a parameter to a procedure. Is this right? Is there a better solution to do this? Can anyone help me please?

Thank you!

Personally, I always use XML for this type of thing. You can then use OPENXML in the stored procedure to turn the xml into a table that you can join to.

Here is a great article about your choices for passing an array to a stored procedure.

http://www.sommarskog.se/arrays-in-sql.html

|||Thank you Ryan!