Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Scripting a stored procedure as a view Expand / Collapse
Author
Message
Posted Thursday, August 15, 2013 1:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 1:05 PM
Points: 4, Visits: 52
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 
@UnitQtyPlaces DecimalPlacesType,
@UseEffD FlagNyType,
@RunBasis RunBasisType,
@ShowInternal FlagNyType = 0,
@ShowExternal FlagNyType = 1,
@PrintItemMaterials ListYesNoType = 1,
@EffectiveDate DateType = Null,
@DisplayReferenceFields ListYesNoType = 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

Post #1484903
Posted Friday, August 16, 2013 10:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:23 AM
Points: 1,053, Visits: 2,562
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
Blog: SQLSouth
Twitter: @SQLSouth
Post #1485311
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse