多表查询存储过程 ；看懂了应该会有所收获
1 -- Description: 根据条件查询金融产品信息。 2 -- ============================================= 3 ALTER PROCEDURE [dbo].[SearchProduct] 4 ( 5 @ben1 AS DECIMAL(38, 2) , 6 @yue1 AS INT , 7 @strWhere AS NVARCHAR(MAX), 8 @PageSize as int, 9 @PageIndex as int 10 ) 11 AS 12 BEGIN 13 14 SET NOCOUNT ON ; 15 DECLARE @str NVARCHAR(MAX) 16 17 SELECT DISTINCT 18 p.ProductID , 19 p.ProductName , 20 i.InstitutionName , 21 i.IconUrl , 22 i.InstitutionName + ' - ' + p.ProductName AS PNames , 23 --pt.PledgeName , 24 CASE WHEN CHARINDEX('企业主', ISNULL(c.ConditionContent, '')) > 0 25 AND CHARINDEX('个体户', ISNULL(c.ConditionContent, '')) > 0 26 AND CHARINDEX('上班族', ISNULL(c.ConditionContent, '')) > 0 27 THEN '企业主，个体户，上班族' 28 WHEN CHARINDEX('企业主', ISNULL(c.ConditionContent, '')) > 0 29 AND CHARINDEX('个体户', ISNULL(c.ConditionContent, '')) > 0 30 THEN '企业主，个体户' 31 WHEN CHARINDEX('企业主', ISNULL(c.ConditionContent, '')) > 0 32 AND CHARINDEX('上班族', ISNULL(c.ConditionContent, '')) > 0 33 THEN '企业主，上班族' 34 WHEN CHARINDEX('个体户', ISNULL(c.ConditionContent, '')) > 0 35 AND CHARINDEX('上班族', ISNULL(c.ConditionContent, '')) > 0 36 THEN '个体户，上班族' 37 WHEN CHARINDEX('企业主', ISNULL(c.ConditionContent, '')) > 0 38 AND CHARINDEX('个体户', ISNULL(c.ConditionContent, '')) = -1 39 AND CHARINDEX('上班族', ISNULL(c.ConditionContent, '')) = -1 40 THEN '企业主' 41 WHEN CHARINDEX('企业主', ISNULL(c.ConditionContent, '')) = -1 42 AND CHARINDEX('个体户', ISNULL(c.ConditionContent, '')) > 0 43 AND CHARINDEX('上班族', ISNULL(c.ConditionContent, '')) = -1 44 THEN '个体户' 45 WHEN CHARINDEX('企业主', ISNULL(c.ConditionContent, '')) = -1 46 AND CHARINDEX('个体户', ISNULL(c.ConditionContent, '')) = -1 47 AND CHARINDEX('上班族', ISNULL(c.ConditionContent, '')) > 0 48 THEN '上班族' 49 ELSE '无身份要求' 50 END AS Identitys , 51 c.ConditionContent , 52 LoanTime , 53 CASE WHEN ( POWER(( 1 + MothRateMin ), @yue1) - 1 ) <> 0 54 THEN ( @ben1 * MothRateMin * POWER(( 1 + MothRateMin ), 55 @yue1) ) / ( POWER(( 1 56 + MothRateMin ), 57 @yue1) - 1 ) 58 ELSE 0 59 END AS yuegong , 60 CASE WHEN ( POWER(( 1 + MothRateMin ), @yue1) - 1 ) <> 0 61 THEN ( ( @ben1 * MothRateMin * POWER(( 1 + MothRateMin ), 62 @yue1) ) 63 / ( POWER(( 1 + MothRateMin ), @yue1) - 1 ) ) 64 * @yue1 - @ben1 65 ELSE 0 66 END AS SumRateMin , 67 case when 68 charindex('',isnull(c.ConditionContent,'')) <=0 69 or charindex('',isnull(c.ConditionContent,''),charindex('',isnull(c.ConditionContent,''))+1)<=0 70 or charindex('',isnull(c.ConditionContent,''), charindex('',isnull(c.ConditionContent,''),charindex('',isnull(c.ConditionContent,''))+1)+1)<=0 or 71 ( charindex('',isnull(c.ConditionContent,''), charindex('',isnull(c.ConditionContent,''), charindex('',isnull(c.ConditionContent,''))+1)+1)+4 ) <=0 72 then c.ConditionContent 73 else 74 SUBSTRING(isnull(c.ConditionContent,''),1,charindex('',isnull(c.ConditionContent,''), charindex('',isnull(c.ConditionContent,''), charindex('',isnull(c.ConditionContent,''))+1)+1)+4) end as ConditionContents, 75 e.TypeName , 76 pt.PledgeName , 77 CAST(LimitMin AS VARCHAR) + ' ~ ' + CAST(LimitMax AS VARCHAR) AS LimitMin , 78 CAST(DeadLineMin AS VARCHAR) + ' ~ ' 79 + CAST(DeadLineMax AS VARCHAR) AS DeadLineMin , 80 m.RepaymentName , 81 MothRateMin AS RateMin , 82 CAST(MothRateMin AS VARCHAR) + ' %~ ' 83 + CAST(MothRateMax AS VARCHAR) + '%' AS MothRateMins , 84 CASE p.SynthesizeRate 85 WHEN '确切' 86 THEN CAST(RateMin AS VARCHAR) + '% ~ ' 87 + CAST(RateMax AS VARCHAR) + '%' 88 ELSE p.SynthesizeRate 89 END AS SynthesizeRate , 90 CASE p.SecurityCost 91 WHEN '确切' 92 THEN CAST(CostMin AS VARCHAR) + '% ~ ' 93 + CAST(CostMax AS VARCHAR) + '%' 94 ELSE p.SecurityCost 95 END AS SecurityCost , 96 CASE OneTimeFee 97 WHEN '确切' 98 THEN CAST(FeeMin AS VARCHAR) + '% ~ ' 99 + CAST(FeeMax AS VARCHAR) + '%'100 ELSE OneTimeFee101 END AS OneTimeFee102 FROM dbo.P_LoanProduct p 103 LEFT JOIN dbo.I_Institution i ON p.InstitutionID = i.InstitutionID104 LEFT JOIN dbo.I_InstitutionType t ON i.InstitutioniTypeID = t.InstitutioniTypeID105 LEFT JOIN dbo.P_PledgeType pt ON p.PledgeID = pt.PledgeID106 LEFT JOIN dbo.P_ProductType e ON p.ProductTypeID = e.ProductTypeID107 LEFT JOIN dbo.P_RepaymentMode m ON p.ModeID = m.ModeID108 LEFT JOIN dbo.P_Product_Condition pc ON p.ProductID = pc.ProductID109 LEFT JOIN dbo.P_Condition c ON pc.ConditionID = c.ConditionID110 LEFT JOIN dbo.P_ConditionCredit ct ON c.ConditionID = ct.ConditionID111 LEFT JOIN dbo.P_ConditionProperty py ON c.ConditionID = py.ConditionID112 LEFT JOIN dbo.P_ResidenceAge re ON c.ConditionID = re.ConditionID113 LEFT JOIN dbo.P_WorkAge w ON c.ConditionID = w.ConditionID114 LEFT JOIN dbo.P_ShopAge s ON c.ConditionID = s.ConditionID115 LEFT JOIN dbo.P_Condition_Certificate cc ON c.ConditionID = cc.ConditionID116 LEFT JOIN dbo.P_ConditionOwning co ON c.ConditionID = co.ConditionID117 LEFT JOIN dbo.P_CIManageAge ca ON c.ConditionID = ca.ConditionID118 LEFT JOIN dbo.P_ConditionManage cm ON c.ConditionID = cm.ConditionID119 LEFT JOIN dbo.P_ConditionIdentity ci ON c.ConditionID = ci.ConditionID120 LEFT JOIN dbo.P_ConditionPlace cp ON c.ConditionID = cp.ConditionID 121 122 123 END124 --exec dbo.SearchProduct 10000.00,12,' ' ,3,1 dal层 多表查询
1 ///2 /// 分页获取数据列表json 3 /// 4 public string GetListByPageJsonn( decimal ben, int yue,int PageSize, int PageIndex, string strWhere) 5 { 6 DataSet Ds = new DataSet(); 7 StringBuilder strSql = new StringBuilder(); 8 9 strSql.Append(" declare @yue1 int , @ben1 decimal(38,2) set @yue1= ");10 strSql.Append(yue);11 12 strSql.Append(" set @ben1 =");13 strSql.Append(ben);14 15 strSql.Append(" SELECT DISTINCT p.ProductID ,p.ProductName ,i.InstitutionName ,i.IconUrl ,i.InstitutionName + ' - ' + p.ProductName AS PNames , CASE WHEN CHARINDEX('企业主', ISNULL(c.ConditionContent, '')) > 0 AND CHARINDEX('个体户', ISNULL(c.ConditionContent, '')) > 0 AND CHARINDEX('上班族', ISNULL(c.ConditionContent, '')) > 0 THEN '企业主，个体户，上班族' WHEN CHARINDEX('企业主', ISNULL(c.ConditionContent, '')) > 0 AND CHARINDEX('个体户', ISNULL(c.ConditionContent, '')) > 0 THEN '企业主，个体户' WHEN CHARINDEX('企业主', ISNULL(c.ConditionContent, '')) > 0 AND CHARINDEX('上班族', ISNULL(c.ConditionContent, '')) > 0 THEN '企业主，上班族' WHEN CHARINDEX('个体户', ISNULL(c.ConditionContent, '')) > 0 AND CHARINDEX('上班族', ISNULL(c.ConditionContent, '')) > 0 THEN '个体户，上班族' WHEN CHARINDEX('企业主', ISNULL(c.ConditionContent, '')) > 0 AND CHARINDEX('个体户', ISNULL(c.ConditionContent, '')) = -1 AND CHARINDEX('上班族', ISNULL(c.ConditionContent, '')) = -1 THEN '企业主' WHEN CHARINDEX('企业主', ISNULL(c.ConditionContent, '')) = -1 AND CHARINDEX('个体户', ISNULL(c.ConditionContent, '')) > 0 AND CHARINDEX('上班族', ISNULL(c.ConditionContent, '')) = -1 THEN '个体户' WHEN CHARINDEX('企业主', ISNULL(c.ConditionContent, '')) = -1 AND CHARINDEX('个体户', ISNULL(c.ConditionContent, '')) = -1 AND CHARINDEX('上班族', ISNULL(c.ConditionContent, '')) > 0 THEN '上班族' ELSE '无身份要求' END AS Identitys , c.ConditionContent , LoanTime , CASE WHEN ( POWER(( 1 + MothRateMin ), @yue1) - 1 ) <> 0 THEN ( @ben1 * MothRateMin * POWER(( 1 + MothRateMin ), @yue1) ) / ( POWER(( 1 + MothRateMin ), @yue1) - 1 )ELSE 0 END AS yuegong , CASE WHEN ( POWER(( 1 + MothRateMin ), @yue1) - 1 ) <> 0 THEN ( ( @ben1 * MothRateMin * POWER(( 1 + MothRateMin ),@yue1) )/ ( POWER(( 1 + MothRateMin ), @yue1) - 1 ) )* @yue1 - @ben1 ELSE 0 END AS SumRateMin , case when charindex('',isnull(c.ConditionContent,'')) <=0 or charindex('',isnull(c.ConditionContent,''),charindex('',isnull(c.ConditionContent,''))+1)<=0 or charindex('',isnull(c.ConditionContent,''), charindex('',isnull(c.ConditionContent,''),charindex('',isnull(c.ConditionContent,''))+1)+1)<=0 or ( charindex('',isnull(c.ConditionContent,''), charindex('',isnull(c.ConditionContent,''), charindex('',isnull(c.ConditionContent,''))+1)+1)+4 ) <=0 then c.ConditionContent else SUBSTRING(isnull(c.ConditionContent,''),1,charindex('',isnull(c.ConditionContent,''), charindex('',isnull(c.ConditionContent,''), charindex('',isnull(c.ConditionContent,''))+1)+1)+4) end as ConditionContents, e.TypeName ,pt.PledgeName ,CAST(LimitMin AS VARCHAR) + ' ~ ' + CAST(LimitMax AS VARCHAR) AS LimitMin ,CAST(DeadLineMin AS VARCHAR) + ' ~ ' + CAST(DeadLineMax AS VARCHAR) AS DeadLineMin ,m.RepaymentName ,MothRateMin AS RateMin ,CAST(MothRateMin AS VARCHAR) + ' %~ '+ CAST(MothRateMax AS VARCHAR) + '%' AS MothRateMins ,CASE p.SynthesizeRate WHEN '确切' THEN CAST(RateMin AS VARCHAR) + '% ~ '+ CAST(RateMax AS VARCHAR) + '%' ELSE p.SynthesizeRate END AS SynthesizeRate ,CASE p.SecurityCost WHEN '确切' THEN CAST(CostMin AS VARCHAR) + '% ~ ' + CAST(CostMax AS VARCHAR) + '%' ELSE p.SecurityCost END AS SecurityCost , CASE OneTimeFee WHEN '确切' THEN CAST(FeeMin AS VARCHAR) + '% ~ ' + CAST(FeeMax AS VARCHAR) + '%' ELSE OneTimeFee END AS OneTimeFee FROM dbo.P_LoanProduct p LEFT JOIN dbo.I_Institution i ON p.InstitutionID = i.InstitutionID LEFT JOIN dbo.I_InstitutionType t ON i.InstitutioniTypeID = t.InstitutioniTypeID LEFT JOIN dbo.P_PledgeType pt ON p.PledgeID = pt.PledgeID LEFT JOIN dbo.P_ProductType e ON p.ProductTypeID = e.ProductTypeID LEFT JOIN dbo.P_RepaymentMode m ON p.ModeID = m.ModeID LEFT JOIN dbo.P_Product_Condition pc ON p.ProductID = pc.ProductID LEFT JOIN dbo.P_Condition c ON pc.ConditionID = c.ConditionID LEFT JOIN dbo.P_ConditionCredit ct ON c.ConditionID = ct.ConditionID LEFT JOIN dbo.P_ConditionProperty py ON c.ConditionID = py.ConditionID LEFT JOIN dbo.P_ResidenceAge re ON c.ConditionID = re.ConditionID LEFT JOIN dbo.P_WorkAge w ON c.ConditionID = w.ConditionID LEFT JOIN dbo.P_ShopAge s ON c.ConditionID = s.ConditionID LEFT JOIN dbo.P_Condition_Certificate cc ON c.ConditionID = cc.ConditionID LEFT JOIN dbo.P_ConditionOwning co ON c.ConditionID = co.ConditionID LEFT JOIN dbo.P_CIManageAge ca ON c.ConditionID = ca.ConditionID LEFT JOIN dbo.P_ConditionManage cm ON c.ConditionID = cm.ConditionID LEFT JOIN dbo.P_ConditionIdentity ci ON c.ConditionID = ci.ConditionID LEFT JOIN dbo.P_ConditionPlace cp ON c.ConditionID = cp.ConditionID ");16 if (strWhere.Trim() != "")17 {18 strSql.Append(" where " + strWhere);19 }20 DbHelperSQL.GotDataList(strSql.ToString(), "DataList", Ds, PageIndex * PageSize, PageSize);//分页后的数据21 return DbHelperSQL.ToJson(Ds.Tables[0], GetRecordCountn(ben,yue, strWhere));22 }23 24 ///25 /// 获取记录总数26 /// 27 public int GetRecordCountn(decimal ben, int yue, string strWhere)28 {29 DataSet Ds = new DataSet();30 StringBuilder strSql = new StringBuilder();31 32 strSql.Append(" declare @yue1 int , @ben1 decimal(38,2) set @yue1= ");33 strSql.Append(yue);34 35 strSql.Append(" set @ben1 =");36 strSql.Append(ben);37 strSql.Append("select count(1) from ( ");38 strSql.Append(" SELECT DISTINCT p.ProductID ,p.ProductName ,i.InstitutionName ,i.IconUrl ,i.InstitutionName + ' - ' + p.ProductName AS PNames , CASE WHEN CHARINDEX('企业主', ISNULL(c.ConditionContent, '')) > 0 AND CHARINDEX('个体户', ISNULL(c.ConditionContent, '')) > 0 AND CHARINDEX('上班族', ISNULL(c.ConditionContent, '')) > 0 THEN '企业主，个体户，上班族' WHEN CHARINDEX('企业主', ISNULL(c.ConditionContent, '')) > 0 AND CHARINDEX('个体户', ISNULL(c.ConditionContent, '')) > 0 THEN '企业主，个体户' WHEN CHARINDEX('企业主', ISNULL(c.ConditionContent, '')) > 0 AND CHARINDEX('上班族', ISNULL(c.ConditionContent, '')) > 0 THEN '企业主，上班族' WHEN CHARINDEX('个体户', ISNULL(c.ConditionContent, '')) > 0 AND CHARINDEX('上班族', ISNULL(c.ConditionContent, '')) > 0 THEN '个体户，上班族' WHEN CHARINDEX('企业主', ISNULL(c.ConditionContent, '')) > 0 AND CHARINDEX('个体户', ISNULL(c.ConditionContent, '')) = -1 AND CHARINDEX('上班族', ISNULL(c.ConditionContent, '')) = -1 THEN '企业主' WHEN CHARINDEX('企业主', ISNULL(c.ConditionContent, '')) = -1 AND CHARINDEX('个体户', ISNULL(c.ConditionContent, '')) > 0 AND CHARINDEX('上班族', ISNULL(c.ConditionContent, '')) = -1 THEN '个体户' WHEN CHARINDEX('企业主', ISNULL(c.ConditionContent, '')) = -1 AND CHARINDEX('个体户', ISNULL(c.ConditionContent, '')) = -1 AND CHARINDEX('上班族', ISNULL(c.ConditionContent, '')) > 0 THEN '上班族' ELSE '无身份要求' END AS Identitys , c.ConditionContent , LoanTime , CASE WHEN ( POWER(( 1 + MothRateMin ), @yue1) - 1 ) <> 0 THEN ( @ben1 * MothRateMin * POWER(( 1 + MothRateMin ), @yue1) ) / ( POWER(( 1 + MothRateMin ), @yue1) - 1 )ELSE 0 END AS yuegong , CASE WHEN ( POWER(( 1 + MothRateMin ), @yue1) - 1 ) <> 0 THEN ( ( @ben1 * MothRateMin * POWER(( 1 + MothRateMin ),@yue1) )/ ( POWER(( 1 + MothRateMin ), @yue1) - 1 ) )* @yue1 - @ben1 ELSE 0 END AS SumRateMin , case when charindex('',isnull(c.ConditionContent,'')) <=0 or charindex('',isnull(c.ConditionContent,''),charindex('',isnull(c.ConditionContent,''))+1)<=0 or charindex('',isnull(c.ConditionContent,''), charindex('',isnull(c.ConditionContent,''),charindex('',isnull(c.ConditionContent,''))+1)+1)<=0 or ( charindex('',isnull(c.ConditionContent,''), charindex('',isnull(c.ConditionContent,''), charindex('',isnull(c.ConditionContent,''))+1)+1)+4 ) <=0 then c.ConditionContent else SUBSTRING(isnull(c.ConditionContent,''),1,charindex('',isnull(c.ConditionContent,''), charindex('',isnull(c.ConditionContent,''), charindex('',isnull(c.ConditionContent,''))+1)+1)+4) end as ConditionContents, e.TypeName ,pt.PledgeName ,CAST(LimitMin AS VARCHAR) + ' ~ ' + CAST(LimitMax AS VARCHAR) AS LimitMin ,CAST(DeadLineMin AS VARCHAR) + ' ~ ' + CAST(DeadLineMax AS VARCHAR) AS DeadLineMin ,m.RepaymentName ,MothRateMin AS RateMin ,CAST(MothRateMin AS VARCHAR) + ' %~ '+ CAST(MothRateMax AS VARCHAR) + '%' AS MothRateMins ,CASE p.SynthesizeRate WHEN '确切' THEN CAST(RateMin AS VARCHAR) + '% ~ '+ CAST(RateMax AS VARCHAR) + '%' ELSE p.SynthesizeRate END AS SynthesizeRate ,CASE p.SecurityCost WHEN '确切' THEN CAST(CostMin AS VARCHAR) + '% ~ ' + CAST(CostMax AS VARCHAR) + '%' ELSE p.SecurityCost END AS SecurityCost , CASE OneTimeFee WHEN '确切' THEN CAST(FeeMin AS VARCHAR) + '% ~ ' + CAST(FeeMax AS VARCHAR) + '%' ELSE OneTimeFee END AS OneTimeFee FROM dbo.P_LoanProduct p LEFT JOIN dbo.I_Institution i ON p.InstitutionID = i.InstitutionID LEFT JOIN dbo.I_InstitutionType t ON i.InstitutioniTypeID = t.InstitutioniTypeID LEFT JOIN dbo.P_PledgeType pt ON p.PledgeID = pt.PledgeID LEFT JOIN dbo.P_ProductType e ON p.ProductTypeID = e.ProductTypeID LEFT JOIN dbo.P_RepaymentMode m ON p.ModeID = m.ModeID LEFT JOIN dbo.P_Product_Condition pc ON p.ProductID = pc.ProductID LEFT JOIN dbo.P_Condition c ON pc.ConditionID = c.ConditionID LEFT JOIN dbo.P_ConditionCredit ct ON c.ConditionID = ct.ConditionID LEFT JOIN dbo.P_ConditionProperty py ON c.ConditionID = py.ConditionID LEFT JOIN dbo.P_ResidenceAge re ON c.ConditionID = re.ConditionID LEFT JOIN dbo.P_WorkAge w ON c.ConditionID = w.ConditionID LEFT JOIN dbo.P_ShopAge s ON c.ConditionID = s.ConditionID LEFT JOIN dbo.P_Condition_Certificate cc ON c.ConditionID = cc.ConditionID LEFT JOIN dbo.P_ConditionOwning co ON c.ConditionID = co.ConditionID LEFT JOIN dbo.P_CIManageAge ca ON c.ConditionID = ca.ConditionID LEFT JOIN dbo.P_ConditionManage cm ON c.ConditionID = cm.ConditionID LEFT JOIN dbo.P_ConditionIdentity ci ON c.ConditionID = ci.ConditionID LEFT JOIN dbo.P_ConditionPlace cp ON c.ConditionID = cp.ConditionID ");39 if (strWhere.Trim() != "")40 {41 strSql.Append(" where " + strWhere);42 }43 strSql.Append(") as a");44 object obj = DbHelperSQL.GetSingle(strSql.ToString());45 if (obj == null)46 {47 return 0;48 }49 else50 {51 return Convert.ToInt32(obj);52 }53 }
事务回滚：
1 BEGIN 2 begin try 3 begin transaction tr 4 SET NOCOUNT ON; 5 declare @helixi decimal(18,6) 6 exec @helixi = dbo.yueBen @ben,@yue,@li 7 set @helixi=@helixi*@yue-@ben --set 8 9 commit transaction tr return @helixi10 end try11 begin catch 12 13 14 --print ERROR_MESSAGE()15 return 016 rollback transaction 17 18 end catch19 END