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!
