August 15, 2013 at 1:52 pm
I have a stored procedure with 17 parameters. Not all 17 parameters are needed for the intended use so I have narrowed the scope of parameters down to 8. Ultimately I would like to remove all of the parameters. Once it is all said and done I can reference the view when needed and it will show the exact same data as the stored procedure. The code below is what I have so far. Any and all advice is appreciated.
DECLARE
@UnitQtyPlacesDecimalPlacesType,
@UseEffDFlagNyType,
@RunBasisRunBasisType,
@ShowInternalFlagNyType = 0,
@ShowExternalFlagNyType = 1,
@PrintItemMaterialsListYesNoType = 1,
@EffectiveDateDateType = Null,
@DisplayReferenceFieldsListYesNoType = Null
SELECT @UnitQtyPlaces = places_qty_per FROM invparms
SELECT @RunBasis = sfcparms.run_basis FROM sfcparms
SELECT
item.item,
item.description,
item.revision,
jobroute.oper_num,
jobroute.wc,
Case
When @RunBasis = jobroute.run_basis_lbr
Then ' '
Else jobroute.run_basis_lbr
End as run_basis_lbr_marker,
Case
When @RunBasis = jobroute.run_basis_mch
Then ' '
Else jobroute.run_basis_mch
End as run_basis_mch_marker,
jobroute.run_basis_lbr,
jobroute.run_basis_mch,
JobrouteNoteExists = dbo.ReportNotesExist('jobroute', jobroute.RowPointer, @ShowInternal,
@ShowExternal, jobroute.NoteExistsFlag),
jobroute.rowpointer as jobroute_rowpointer,
wc.description as wc_description,
jrt_sch.move_ticks / 100 as move_hours,
jrt_sch.queue_ticks / 100 as queue_hours,
jrt_sch.setup_ticks / 100 as setup_hours,
jrt_sch.sched_ticks / 100 as fix_sch_hours,
jrt_sch.sched_off / 100 as offset_hrs,
jrt_sch.pcs_per_lbr_hr,
jrt_sch.pcs_per_mch_hr,
jrt_sch.run_ticks_mch / 100 as mch_hr_per_pc,
jrt_sch.run_ticks_lbr / 100 as lbr_hr_per_pc,
jobroute.cntrl_point,
jobmatl.sequence as jobmatl_sequence,
jobmatl.matl_type,
jobmatl.item as jobmatl_item,
Case
When x_item.description Is Null
Then jobmatl.description
Else x_item.description
End as jobmatl_description,
x_item.revision as x_item_revision,
jobmatl.units,
jobmatl.matl_qty_conv,
jobmatl.u_m,
jobmatl.ref_type,
jobmatl.effect_date,
jobmatl.obs_date,
jobmatl.bom_seq,
JobmatlNoteExists = dbo.ReportNotesExist('jobmatl', jobmatl.RowPointer, @ShowInternal,
@ShowExternal, jobmatl.NoteExistsFlag),
jobmatl.rowpointer as jobmatl_rowpointer,
jobmatl.alt_group,
jobmatl.alt_group_rank,
job_ref.sequence as job_ref_sequence,
job_ref.ref_des,
job_ref.bubble,
job_ref.assy_seq,
@UnitQtyPlaces AS 'UnitQtyPlaces'
FROM item
INNER JOIN jobroute ON item.job = jobroute.job AND item.suffix = jobroute.suffix
Left Outer JOIN jobmatl ON @PrintItemMaterials = 1 and jobroute.job = jobmatl.job AND jobroute.suffix = jobmatl.suffix AND
jobroute.oper_num = jobmatl.oper_num And
Case When @UseEffD = 1 Then
Case
When jobmatl.effect_date is Null Then 1
When jobmatl.effect_date <= @EffectiveDate Then 1
Else 0
End
Else 1
End = 1 AND
Case When @UseEffD = 1 Then
Case
When jobmatl.obs_date Is Null Then 1
When jobmatl.obs_date > @EffectiveDate Then 1
Else 0
End
Else 1
End = 1
Left Outer Join jrt_sch on jobroute.job = jrt_sch.job and jobroute.suffix = jrt_sch.suffix and
jobroute.oper_num = jrt_sch.oper_num
Left Outer Join wc on jobroute.wc = wc.wc
Left Outer Join job_ref on @DisplayReferenceFields = 1 and @PrintItemMaterials = 1 and
jobmatl.job = job_ref.job and jobmatl.suffix = job_ref.suffix and
jobmatl.oper_num = job_ref.oper_num and jobmatl.sequence = job_ref.sequence
Left Outer Join item as x_item on @PrintItemMaterials = 1 and jobmatl.item = x_item.item
ORDER BY
item.item,
jobroute.oper_num,
jobmatl.alt_group,
jobmatl.alt_group_rank,
job_ref.ref_seq
August 16, 2013 at 10:48 am
You can't declare variables in a view, and of course views can't take parameters. You use variables in number of places:
-- as a column and in CASE expressions in the SELECT clause;
-- as parameters to functions called in the SELECT clause;
-- in the join conditions of almost every joined table or view.
What will you use for those values in a view?
If you need an object that can be used in the FROM clause of a select statement and also accepts parameters that can be used in the query, you could turn this code into an inline table-valued function.
As an aside, calling scalar functions in a SELECT clause can really hurt performance. You call the dbo.reportNotesExist function twice in your SELECT clause. That function will be called twice for every row that the query returns - if you get 1,000 rows, the function will have to run 2,000 times.
Jason Wolfkill
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply