One Big SP How To?

[ajacode]

As a Newbie, I have used several views which each use a preceding view until the final view is used by a stored procedure which feeds several reports.

I know this should be able to include all steps into a single SP, but I'm not sure of the construction of such an SP

Can anyone give me a clue on how to "glue" all these into a single SP ?

Thanks so much beforehand:

/++++++++++++++++++++++++++++++++++++++++++++++++++++++++/


/View2/

SELECT GL.cmpny_cd, GL.trn_dt, GL.job_no, GL.phase_no,
GL.cost_no, GL.cost_type, GL.trn_amt, GL.job_hrs,
GL.trn_desc, C.WeekNO
FROM dbo.GLDetail GL INNER JOIN
dbo.tblCalenderRef C ON GL.trn_dt = C.RefDate INNER JOIN
dbo.FRJobs FRJ ON GL.cmpny_cd = FRJ.FRcmpny_cd AND
GL.job_no = FRJ.FRjob_no
WHERE (GL.cost_type IN ('O', 'L', 'M', 'S', 'E')) AND
(GL.trn_desc <> 'Reg Invoice') AND (FRJ.FR_Is_Alloc <> 0)







/*--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

--View2_Step2*/

SELECT C.WeekNO, View2.cmpny_cd, View2.job_no,
View2.phase_no, View2.cost_no, View2.cost_type,
SUM(View2.trn_amt) AS Amt, SUM(View2.job_hrs) AS Hrs,
C.RefDate AS WeekOf
FROM dbo.tblCalenderRef C INNER JOIN
dbo.View2 ON C.WeekNO = dbo.View2.WeekNO
WHERE (C.RefDayOfWk = N'M')
GROUP BY C.WeekNO, dbo.View2.cmpny_cd, dbo.View2.job_no,
dbo.View2.phase_no, dbo.View2.cost_no, dbo.View2.cost_type,
C.RefDate




/*--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-- View2_Step3*/

SELECT C.RefDate AS WeekOf, View2_Step2.cmpny_cd,
View2_Step2.job_no, View2_Step2.phase_no,
View2_Step2.cost_no, View2_Step2.cost_type,
View2_Step2.Amt, C.RefDayOfWk
FROM dbo.View2_Step2 INNER JOIN
dbo.tblCalenderRef C ON
dbo.View2_Step2.WeekNO = C.WeekNO
WHERE (C.RefDayOfWk = N'M')





-- View2_Step4

SELECT View2_Step3.WeekOf AS week_of, View2_Step3.cmpny_cd,
View2_Step3.job_no, View2_Step3.phase_no,
View2_Step3.cost_no, View2_Step3.cost_type,
View2_Step3.Amt AS ActualCost, FR.c_est_orig_budget,
FR.c_est_qty_orig_projections, FR.LbrHrs_project, FR.LbrHrs,
FR.c_est_projections
FROM dbo.FRJobCostAllocate FR INNER JOIN
dbo.View2_Step3 ON
FR.week_of <= dbo.View2_Step3.WeekOf AND
FR.cmpny_cd = dbo.View2_Step3.cmpny_cd AND
FR.job_no = dbo.View2_Step3.job_no AND
FR.phase_no = dbo.View2_Step3.phase_no AND
FR.cost_no = dbo.View2_Step3.cost_no AND
FR.Cost_type = dbo.View2_Step3.cost_type
GROUP BY dbo.View2_Step3.cmpny_cd, dbo.View2_Step3.job_no,
dbo.View2_Step3.phase_no, dbo.View2_Step3.cost_no,
dbo.View2_Step3.cost_type, dbo.View2_Step3.WeekOf,
FR.c_est_orig_budget, dbo.View2_Step3.Amt,
FR.c_est_qty_orig_projections, FR.LbrHrs_project, FR.LbrHrs,
FR.c_est_projections





/*--+++++++++++++++++++++++++++++++++++++++++++++++++++++

--View2_Step5*/

SELECT DISTINCT
cmpny_cd, job_no, phase_no, cost_no, cost_type, ActualCost,
c_est_orig_budget, week_of, c_est_qty_orig_projections,
LbrHrs_project
FROM dbo.View2_Step4
GROUP BY job_no, phase_no, cost_no, cost_type, ActualCost,
c_est_orig_budget, cmpny_cd, week_of,
c_est_qty_orig_projections, LbrHrs_project



/*--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- View2_Final*/

SELECT DISTINCT
FRCmpnyMst.FRcmpny_name, View2_Step5.cmpny_cd,
JCJobs.job_name, View2_Step5.job_no,
View2_Step5.phase_no, JCCostMast.phase_desc,
View2_Step5.cost_no, View2_Step5.Cost_type,
View2_Step5.week_of, View2_Step5.ActualCost,
SUM(View2_Step5.c_est_orig_budget) AS c_est_orig_budget,
SUM(View2_Step5.LbrHrs_project) AS L_projections,
SUM(View2_Step5.c_est_qty_orig_projections)
AS c_projections
FROM dbo.View2_Step5 INNER JOIN
dbo.FRCmpnyMst ON
dbo.View2_Step5.cmpny_cd = dbo.FRCmpnyMst.FRcmpny_cd INNER
JOIN
dbo.JCCostMast ON
dbo.View2_Step5.phase_no = dbo.JCCostMast.phase_no AND
dbo.View2_Step5.job_no = dbo.JCCostMast.job_no AND
dbo.View2_Step5.cmpny_cd = dbo.JCCostMast.cmpny_cd AND
dbo.View2_Step5.cost_no = dbo.JCCostMast.cost_no AND
dbo.View2_Step5.Cost_type = dbo.JCCostMast.cost_type INNER
JOIN
dbo.JCJobs ON
dbo.JCCostMast.job_no = dbo.JCJobs.job_no AND
dbo.JCCostMast.cmpny_cd = dbo.JCJobs.cmpny_cd
GROUP BY dbo.FRCmpnyMst.FRcmpny_name,
dbo.View2_Step5.cmpny_cd, dbo.JCJobs.job_name,
dbo.View2_Step5.job_no, dbo.View2_Step5.phase_no,
dbo.JCCostMast.phase_desc, dbo.View2_Step5.cost_no,
dbo.View2_Step5.Cost_type, dbo.View2_Step5.week_of,
dbo.View2_Step5.ActualCost


/--+++++++++++++++++++++++++++++++++++++++++++++/


/****** Object: Stored Procedure dbo.sproc_ReportParam_CoNo_JobNO_Dates Script Date: 03/26/2002 7:16:30 PM ******/

/****** Object: Stored Procedure dbo.sproc_ListBoxActiveJobsList Script Date: 03/22/2002 3:28:34 PM ******/
Alter Procedure dbo.sproc_ReportParam_CoNo_JobNO_Dates
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/

@co_cd varchar(2),
@Job_No Varchar (10),
@StartDate DateTime,
@EndDate DateTime

As
set nocount on

SELECT
FRcmpny_name,
cmpny_cd,
job_name,
job_no,
phase_no,
phase_desc,
cost_no,
cost_type,
ActualCost,
c_est_orig_budget,
c_projections,
L_projections,
Week_Of

FROM View2_Final

WHERE (View2_Final.cmpny_cd = @co_cd) AND
(View2_Final.job_no = @job_No) AND
(View2_Final.Week_Of BETWEEN @StartDate AND @EndDate)





return

/ALLOC_JOB_EST/


/****** Object: Stored Procedure dbo.sproc_AllocJobEst Script Date: 03/26/2002 7:16:30 PM ******/

/****** Object: Stored Procedure dbo.sproc_AllocJobEst Script Date: 03/22/2002 3:28:34 PM ******/
Alter Procedure dbo.sproc_AllocJobEst
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
@co_jo varchar(12)

As
set nocount on
-- intError keeps track of current @@ERROR
-- value which may be 0 to indicate no error.
-- intSaveError keeps track of last non-zero
-- @@ERROR value.
-- intRowCount keeps track of rowcount.
DECLARE @intError INT, @intSaveError INT
DECLARE @intRowCount INT
SELECT @intSaveError = 0

BEGIN TRAN
INSERT INTO FRJobCostAllocate
(cmpny_cd,
job_no,
phase_no,
cost_no,
cost_type,
acct_no,
c_est_orig_budget,
c_est_qty_orig_budget,
c_uom,
phase_desc,
week_of,
job_cost_source)

SELECT
A.cmpny_cd,
A.job_no,
A.phase_no,
A.cost_no,
A.cost_type,
A.acct_no,
A.Cost As CstEst,
A.Qty As QtyEst,
A.c_uom,
A.phase_desc,
A.RefDate,
'JE'
FROM [vw_JobAlloc_Step3] A
WHERE A.cmpny_cd+A.job_no = @co_Jo

IF @intError <> 0
BEGIN
SELECT @intSaveError = @intError
PRINT 'Error: ' + CAST(@intError AS VARCHAR(5)) +
' occurred.'
END
ELSE
BEGIN
-- If no row is found in tblCustomer, no error will
-- be generated. However, we can check @@ROWCOUNT
-- to test for this condition. In this case, we
-- create a dummy errror condition.
IF @intRowCount = 0
BEGIN
SELECT @intSaveError = 5000
END
END



/*

After the Allocations for cost are done then the Allocations for estimated Hours are set.


*/


UPDATE FRJobCostAllocate
SET FRJobCostAllocate.LbrHrs = vw_LbrAlloc_Step3.Hrs

FROM FRJobCostAllocate
INNER JOIN
vw_LbrAlloc_Step3 ON
FRJobCostAllocate.cmpny_cd = vw_LbrAlloc_Step3.cmpny_cd AND
FRJobCostAllocate.job_no = vw_LbrAlloc_Step3.job_no AND
FRJobCostAllocate.phase_no = vw_LbrAlloc_Step3.phase_no AND
FRJobCostAllocate.cost_no = vw_LbrAlloc_Step3.cost_no

WHERE FRJobCostAllocate.cost_type = 'L' AND
FRJobCostAllocate.cmpny_cd +FRJobCostAllocate.job_no = @co_jo


/*
And Finally the FRJobs Table is flagged as done allocations for this job.


*/



UPDATE [FRJobs]
SET [FRJobs].FR_Is_Alloc = -1

WHERE FRJobs.FRcmpny_cd+FRJobs.FRjob_no = @co_jo



IF @intSaveError = 0
BEGIN
-- All is well, so commit transaction and
-- report success.
COMMIT TRAN
END
ELSE
BEGIN
-- Something went wrong, so rollback
-- the transaction and report the problem.
ROLLBACK TRAN
END



return




/++++++++++++++++++++++++++++++++++++++++++++++++++++++++/

 

[AWithers]

The basic construction you need is
--
select yourfield from (select yourfield from yourtable) view1
--

i.e. the sql in parentheses is one of your current views and aliased as view1. I'm sure It will get very confusing joining them all together but I think it is possible

Andy