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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MohammedUCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.