请问如何将有子查询并且带参数的存储过程改成视图？

Question

ALTER PROCEDURE [dbo].[P2P_P_getRealStatistics] 
-- Add the parameters for the stored procedure here
    @ObligorName NVARCHAR(50) = '%' ,
    @BusinessModel INT = NULL ,
    @MainContractNO NVARCHAR(50) = '%' ,
    @real_repay_date_min DATETIME = NULL ,
    @real_repay_date_max DATETIME = NULL
AS
    BEGIN
        SELECT  t6.客户名称 ,
                t6.客户类型 ,
                t6.业务类型 ,
                t6.SP ,
                t6.支付表编号 ,
                月租金 ,
                ISNULL(t6.本金, 0) 本金 ,
                ISNULL(t6.利息, 0) 利息 ,
                ISNULL(t6.租金合计, 0) 租金合计 ,
                t6.LoanFlag ,
                ISNULL(t6.手续费, 0) 手续费 ,
                ISNULL(t6.保险费, 0) 保险费 ,
                ISNULL(t6.GPS费, 0) GPS费 ,
                ISNULL(P2P_Loan_ClearCharge.BreachMoney, 0) 违约金 ,
                ISNULL(P2P_Loan_Charge_Info.ChargeSum, 0) 保证金 ,
                ISNULL(t6.租金合计, 0) 实收总额 ,
                ISNULL(t6.利息, 0) 实收总利息 ,
                ISNULL(t6.本金, 0) 实收总本金 ,
                t6.LoanID ,
                t6.LoanTotal 实际放款
        FROM    ( SELECT    t1.ObligorName 客户名称 ,
                            CASE t1.ObligorType
                              WHEN 1 THEN '个人'
                              WHEN 2 THEN '公司'
                            END 客户类型 ,
                            ( SELECT    CodeName
                              FROM      dbo.SYSCode
                              WHERE     dbo.SYSCode.CodeType = 'BusinessModel'
                                        AND CodeValue = t2.BusinessModel
                            ) 业务类型 ,
                            t3.Nickname 'SP' ,
                            t2.MainContractNO 支付表编号 ,
                            ( SELECT    real_repay_benxi
                              FROM      P2P_Loan_Repayment
                              WHERE     P2P_Loan_Repayment.LoanID = t2.LoanID
                                        AND term_preNum = 1
                            ) 月租金 ,
                            ( SELECT    SUM(plan_repay_benjin)
                              FROM      P2P_Loan_Repayment
                              WHERE     P2P_Loan_Repayment.LoanID = t2.LoanID
                                        AND P2P_Loan_Repayment.real_repay_date >= @real_repay_date_min
                                        AND P2P_Loan_Repayment.real_repay_date <= @real_repay_date_max
                            ) 本金 ,
                            ( SELECT    SUM(plan_repay_lixi) real_repay_lixi
                              FROM      P2P_Loan_Repayment
                              WHERE     P2P_Loan_Repayment.LoanID = t2.LoanID
                                        AND P2P_Loan_Repayment.real_repay_date >= @real_repay_date_min
                                        AND P2P_Loan_Repayment.real_repay_date <= @real_repay_date_max
                            ) 利息 ,
                            ( SELECT    SUM(real_repay_benxi) real_repay_benxi
                              FROM      P2P_Loan_Repayment
                              WHERE     P2P_Loan_Repayment.LoanID = t2.LoanID
                                        AND P2P_Loan_Repayment.real_repay_date >= @real_repay_date_min
                                        AND P2P_Loan_Repayment.real_repay_date <= @real_repay_date_max
                            ) 租金合计 ,
                            t2.LoanID ,
                            t2.LoanFlag ,
                            ( SELECT    P2P_Loan_Finance_Info.FinanceSum
                              FROM      dbo.P2P_Loan_Finance_Info
                              WHERE     P2P_Loan_Finance_Info.LoanID = t2.LoanID
                                        AND P2P_Loan_Finance_Info.Item_TypeName = '首付款'
                                        AND dbo.P2P_Loan_Finance_Info.FinanceUnit = 1
                            ) 首付比例 ,
                            ( SELECT    P2P_Loan_Finance_Info.FinanceSum
                              FROM      dbo.P2P_Loan_Finance_Info
                              WHERE     P2P_Loan_Finance_Info.LoanID = t2.LoanID
                                        AND P2P_Loan_Finance_Info.Item_TypeName = '手续费'
                                        AND dbo.P2P_Loan_Finance_Info.FinanceUnit = 0
                            ) 手续费 ,
                            ( SELECT    P2P_Loan_Finance_Info.FinanceSum
                              FROM      dbo.P2P_Loan_Finance_Info
                              WHERE     P2P_Loan_Finance_Info.LoanID = t2.LoanID
                                        AND P2P_Loan_Finance_Info.Item_TypeName = '保险费'
                                        AND dbo.P2P_Loan_Finance_Info.FinanceUnit = 0
                            ) 保险费 ,
                            ( SELECT    P2P_Loan_Finance_Info.FinanceSum
                              FROM      dbo.P2P_Loan_Finance_Info
                              WHERE     P2P_Loan_Finance_Info.LoanID = t2.LoanID
                                        AND P2P_Loan_Finance_Info.Item_TypeName = 'GPS费'
                                        AND dbo.P2P_Loan_Finance_Info.FinanceUnit = 0
                            ) GPS费 ,
                            t4.LoanTotal
                  FROM      P2P_Obligor t1 ,
                            P2P_Loan_Info t2 ,
                            GQShow_UserBase t3 ,
                            P2P_Loan_Process_Info t4
                  WHERE     t1.ObligorType = 1
                            AND t2.LoanUserID = t1.ObligorID
                            AND t3.UserID = t2.FamilyUserID
                            AND t4.Flag = 2
                            AND t4.LoanID = t2.LoanID
                            AND t2.BusinessModel = CASE WHEN @BusinessModel = 0
                                                        THEN t2.BusinessModel
                                                        WHEN @BusinessModel IS NULL
                                                        THEN t2.BusinessModel
                                                        ELSE @BusinessModel
                                                   END
                ) t6
                LEFT JOIN P2P_Loan_ClearCharge ON t6.LoanID = P2P_Loan_ClearCharge.LoanID
                LEFT JOIN P2P_Loan_Charge_Info ON t6.LoanID = P2P_Loan_Charge_Info.ChargeSum
        WHERE   t6.客户名称 LIKE '%' + @ObligorName + '%'
                AND t6.支付表编号 LIKE '%' + @MainContractNO + '%'
                AND 本金 IS NOT NULL
        ORDER BY t6.客户名称;
 

Answer 1

直接写成视图就行，不过要注意的是参数要在select中体现，这样在您查视图的时候可以作为条件写出来。

Answer 2

可以改为表值函数，网上查一下很多的，一个简单的例子：create function dbo.fn_m_org(@org_id numeric(10))
returns table
asreturn(
with org
as
(
select ORG_ID,ORG_NAME,EMP_ID
from  m_org
where org_id = @org_id
) select * from org
)
go
select *
from dbo.fn_m_org(1) o

Answer 3

 动态列名数据视图实现
http://blog.csdn.net/roy_88/article/details/6883078
不建议动态用视图或函数，通过存储过程便于优化

Answer 4

改成left join吧