Thursday, August 15, 2013

[SQL Server] Scripting a stored procedure as a view

[SQL Server] Scripting a stored procedure as a view

Scripting a stored procedure as a view

Posted: 15 Aug 2013 06:52 AM PDT

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.[code="sql"]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 invparmsSELECT @RunBasis = sfcparms.run_basis FROM sfcparmsSELECT 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 itemINNER JOIN jobroute ON item.job = jobroute.job AND item.suffix = jobroute.suffixLeft 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 = 1Left Outer Join jrt_sch on jobroute.job = jrt_sch.job and jobroute.suffix = jrt_sch.suffix and jobroute.oper_num = jrt_sch.oper_numLeft Outer Join wc on jobroute.wc = wc.wcLeft 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.sequenceLeft 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[/code]

Is there a better way to do this? SELECTs within a SELECT

Posted: 15 Aug 2013 02:16 AM PDT

Hello.I am working on a database where we have designed a generic "Addresses" table. We have many classes of records where each type might have multiple addresses. So for example, classes of records could be Salesman. District Manager. Store. Remote Office. Etc. All fictitious but you get the idea. Each record could have one more many addresses so we came up with the universal Address table. I am starting to write test queries to get the data out. The example below does work but I have to think there's a more efficient way to do this and would appreciate feedback. LookUpID is the ID of the record from any given table. Could be SalesPersons. Could be Retail Location. Could be a Investor. Etc. Eventually LookUpID will given a parameter but right now, it's hard coded just to test with. RecordTypeID identifies what table to look in.So in this example, the record in the Addresses Table we are testing against belongs to a SalesPerson. So the logic is, the SalesPerson (or whoever) data would be loaded into the form of the application. We already know the ID of the record whatever type it is. That's the LookUpID. SELECT SalesPersonID, FirstName, LastName,(SELECT Address1 FROM dbo.Addresses WHERE (LookUpID = 1) AND (RecordTypeID = 3)) AS Address1,(SELECT Address2 FROM dbo.Addresses AS Addresses_2 WHERE (LookUpID = 1) AND (RecordTypeID = 3)) AS Address2,(SELECT City FROM dbo.Addresses AS Addresses_3 WHERE (LookUpID = 1) AND (RecordTypeID = 3)) AS City,...etc....FROM dbo.SalesPersonsRecordTypes---------------1 - Owner2 - District Manager3 - SalesPerson4- etc....Thanks for the feedback.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog