Need SQL help

The following views and stored procedures create the Outstanding High Risk Recommendations Report. This is a report of all High Risk recommendations that are On Hold or In Process or Both (In process and On Hold), sorted by Assigned Division. Can you please look at them and suggest a way of creating this report just using the views (without stored procedure)? The status will be selected ny the user from the application and input in the view.

CREATE view v_for_reports
as
SELECT       dbo.IA_recommendation.division
      , dbo.IA_report.title AS [Audit Title]
      , dbo.IA_report.finalized_dt AS [Audit Report Date]
      , dbo.IA_report.type AS [Audit Type]
      , dbo.IA_report.is_confidential_ind AS [Confidential Report]
      , CAST(dbo.IA_report.report_yr AS CHAR(4))
               + '-' + CAST(dbo.IA_report.report_nbr AS CHAR(2)) + '-' + CAST(dbo.IA_recommendation.recommendation_nbr AS CHAR(2))
               AS [Recommendation Number]
      , dbo.IA_recommendation.recommendation
      , dbo.IA_recommendation.risk
      , dbo.IA_recommendation.status
      , IA_response_1.expected_resolution_dt AS [Expected Resolution Date]
      , IA_response_1.response
      , dbo.IA_report.committee_dt AS [Audit Committee Date]
      , dbo.IA_recommendation.completion_dt AS [Date Completed]
      , IA_response_1.creation_dt AS [Date Created]
      , dbo.IA_recommendation.recommendation_id
      , dbo.IA_recommendation.repeat_recommendation AS [Repeat recommendation]

FROM        dbo.IA_report
      inner JOIN dbo.IA_recommendation
      ON dbo.IA_report.report_id = dbo.IA_recommendation.report_id
      inner JOIN dbo.IA_response IA_response_1
      ON dbo.IA_recommendation.recommendation_id = IA_response_1.recommendation_id
WHERE (      IA_response_1.creation_dt =
                   (SELECT MAX(IA_response_2.creation_dt)
                    FROM   dbo.IA_response IA_response_2
                    WHERE IA_response_2.recommendation_id = IA_response_1.recommendation_id))

GO

CREATE view v_outstanding_high_risk_recommendation_report
as
SELECT       dbo.IA_division_ref.division
      , v_for_reports.[Audit Title]
      , v_for_reports.[Audit Report Date]
      , v_for_reports.[Audit Type]
      , v_for_reports.[Confidential Report]
      , v_for_reports.[Recommendation Number]
      , v_for_reports.repeat_recommendation as [Repeat Recommendation]
      , v_for_reports.recommendation, #tmp_v3.risk
      , v_for_reports.status
      , v_for_reports.[Expected Resolution Date]
      , v_for_reports.response, #tmp_v3.[Audit Committee Date]
      , v_for_reports.[Date Completed]
      , v_for_reports.[Date Created]

FROM        dbo.IA_division_ref
      LEFT OUTER JOIN v_for_reports
      ON dbo.IA_division_ref.division = v_for_reports.division

GO

CREATE PROCEDURE sp_rpt_009

      @status Varchar(30)
       AS
IF @status = 'In Process'
BEGIN
          SELECT *
      FROM v_outstanding_high_risk_recommendation_report
      WHERE risk = 'High'
      AND status = 'In Process'
      order by division
END
ELSE if @status = 'On Hold'
BEGIN
          SELECT *
      FROM v_outstanding_high_risk_recommendation_report
      WHERE risk = 'High'
      AND status = 'On Holds'
      order by division
END
ELSE if @status = 'Both'
BEGIN
          SELECT *
      FROM v_outstanding_high_risk_recommendation_report
      WHERE risk = 'High'
      AND status in ('In Process', 'On Hold')
      order by division
END
GO
nupAsked:
Who is Participating?
 
MohammedUConnect With a Mentor Commented:
You can't pass the paramerter to the View so...check the following...

CREATE VIEW Vw_rpt_009
AS
          SELECT *
      FROM v_outstanding_high_risk_recommendation_report
      WHERE risk = 'High'
      AND status in ('In Process', 'On Hold')
      order by division
GO
select * from Vw_rpt_009 where status = 'In Process'
go
select * from Vw_rpt_009 where status = 'On Hold'
go
select * from Vw_rpt_009 where status  in ('In Process', 'On Hold')
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.