问题描述
- SQL语句怎么写成存储过程
-
StringBuilder strSql = new StringBuilder();
StringBuilder strsql2005 = new StringBuilder();
StringBuilder sqlcount = new StringBuilder();
StringBuilder sqlwhere = new StringBuilder();
sqlcount.Append("select count(1) ");
sqlwhere.Append(" SELECT C.XMMC AS XBMC,A.SFZH,A.LXDH,H.LXRDH,H.YLJGBH, ");//需要查询的列
if (strLX == "4" || strLX == "5")
{
sqlwhere.Append(" H.HXDABH,H.JTCYBH,H.DCZ,H.DCRQ,H.CJRQ,H.CJRBH,H.CJR,A.MZBH, YLJGMC,A.GRDABH,A.XM,A.CSNYR,A.JMJKDAH,A.DAZT,H.XXDZ,A.CZLX,Z.XMMC+'-'+W.XMMC AS CZMC,H.SFHG ,X.QZSJ,AZ.JBMC,H.JKJDZT ");//,T.ZZDAH ");//需要查询的列 X.QZSJ,AZ.JBMC}
else
{
sqlwhere.Append(" H.HXDABH,H.JTCYBH,H.DCZ,H.DCRQ,H.CJRQ,H.CJRBH,H.CJR,A.MZBH, YLJGMC,A.GRDABH,A.XM,A.CSNYR,A.JMJKDAH,A.DAZT,H.XXDZ,A.CZLX,Z.XMMC+'-'+W.XMMC AS CZMC,H.SFHG,A.CSNYR as QZSJ,A.XM as JBMC,H.JKJDZT ");//,T.ZZDAH ");
}
sqlwhere.Append(" FROM JMDA_HXDA_JBQK H with(nolock) JOIN JMDA_JTCY A with(nolock) ON A.JTCYBH = H.JTCYBH ");
sqlwhere.Append(" INNER JOIN SKJBXX_SJZDMX C with(nolock) ON C.FLBH = 'S101-01' AND A.XB = C.XMBH ");
sqlwhere.Append(" INNER JOIN SKJBXX_YLJG JG with(nolock) ON JG.YLJGBH=H.YLJGBH ");//社康基本信息_医疗机构表
sqlwhere.Append(" LEFT JOIN SKJBXX_SJZDMX Z with(nolock) ON Z.FLBH = 'JTCY_CZLX' AND A.CZLX =Z.XMBH ");
sqlwhere.Append("LEFT JOIN SKJBXX_SJZDMX W with(nolock) ON W.FLBH='JTCY_CZHK' AND A.CZHK=W.XMBH");
if (strLX == "3")
{
sqlwhere.Append(" LEFT join FNWCQBJ_JBQK F with(nolock) ON A.JTCYBH = F.JMJKDAH And F.DQZT='0' ");
}
else if (strLX == "4" || strLX == "5")
{
sqlwhere.Append(" LEFT JOIN MBGL_JBQKB M with(nolock) ON A.JTCYBH = M.JTCYBH AND M.DAZT='1' ");
sqlwhere.Append(" LEFT JOIN MBGL_JWS X with(nolock) ON M.MBDAH=X.MBDAH ");
sqlwhere.Append("LEFT JOIN SKJBXX_JBXXB AS AZ with(nolock) ON X.JBBH=AZ.JBBH ");
}
else if (strLX == "6")
{
sqlwhere.Append(" LEFT JOIN JSB_JBQK J with(nolock) ON A.JTCYBH = J.JTCYBH AND J.DAZT='1' ");
}
sqlwhere.Append(" WHERE 1=1 ");//档案状态
if (SFZH != "")
{
sqlwhere.Append(" AND A.SFZH = @SFZH ");
}
else
{
if (strJMJKDAH != "")//当个人档案编号不为空时，只根据这个来查询。
{
sqlwhere.Append(" AND A.GRDABH=@GRDABH ");
}
else
{if (lag == "0") { sqlwhere.Append(" AND A.YLJGID = @YLJGBH "); if (YHBH != "") { sqlwhere.Append(" AND H.CJRBH = @CJRBH "); } } else if (lag == "1") { sqlwhere.Append(" AND A.YLJGID LIKE @YLJGBH "); } else if (lag == "2") { } if (strXM != "") { sqlwhere.Append(" AND A.XM like @XM "); } if (BeginDate != Convert.ToDateTime("9999-12-31") && EndDate != Convert.ToDateTime("9999-12-31")) { sqlwhere.Append(" AND H.DCRQ >= @FCJRQ AND H.DCRQ <= @TCJRQ"); } if (CSRQ != Convert.ToDateTime("9999-12-31")) { sqlwhere.Append(" AND A.CSNYR >= @CSNYR "); } if (CSRQE != Convert.ToDateTime("9999-12-31")) { sqlwhere.Append(" AND A.CSNYR <= @CSNYRE "); } if (strXb != "") { sqlwhere.Append(" AND A.XB = @XB "); } if (strHJDZ != null && strHJDZ != "") { sqlwhere.Append(" AND H.XXDZ like @HJDZ "); } if (strXZQ != "") { sqlwhere.Append(" AND A.XZQBH like @XZQ"); } if (strGZDW != "") { sqlwhere.Append(" and H.GZDW = @strGZDW"); } if (CZLX != "0" && CZLX != "") { sqlwhere.Append(" and A.CZLX = @CZLX"); } if (CZHK != "0" && CZHK!="") { if (CZHK == "1") sqlwhere.Append(" and A.CZHK='1'"); else sqlwhere.Append(" and A.CZHK='0'"); } } sqlwhere.Append(" AND A.DAZT = 1 "); switch (strLX) { case "1": sqlwhere.Append(" and datediff(Year,A.CSNYR ,getdate())>=0 and datediff(Year,A.CSNYR ,getdate())<=6 "); //0-6岁儿童档案查询 条件 break; case "2": sqlwhere.Append(" and datediff(Year,A.CSNYR ,getdate())>=65 ");//65岁以上老年人档案查询 条件 break; case "3": sqlwhere.Append(" and F.BJSCBH != '' ");//孕产妇查询条件 break; case "4": sqlwhere.Append(" and X.JBBH='1' "); //慢病高血压 查询条件 break; case "5": sqlwhere.Append(" and X.JBBH='2' ");//慢病糖尿病查询条件 break; case "6": sqlwhere.Append(" and J.JSBDABH!='' ");//重性精神病查询条件 break; default: break; } } sqlwhere.Append(" AND A.DAZT = 1 "); strSql.Append(sqlwhere.ToString()); if (strPX == "1") { strSql.Append(" ORDER BY H.CJRQ DESC,A.XM asc "); } else if (strPX == "2") { strSql.Append(" ORDER BY A.GRDABH DESC "); } else if (strPX == "3") { strSql.Append(" ORDER BY csnyr asc "); } SqlParameter[] parameters = { new SqlParameter("@GRDABH", SqlDbType.VarChar,20), //居民健康档案号 new SqlParameter("@YLJGBH", SqlDbType.VarChar,12), //医疗机构号 new SqlParameter("@XM", SqlDbType.VarChar,40),//姓名 new SqlParameter("@CSNYR", SqlDbType.DateTime),//出生日期 new SqlParameter("@CSNYRE", SqlDbType.DateTime),//出生日期搜索结束时间 new SqlParameter("@FCJRQ", SqlDbType.DateTime),//起始日期 new SqlParameter("@TCJRQ", SqlDbType.DateTime),//结束日期 new SqlParameter("@CJRBH", SqlDbType.VarChar,20),//用户编号 new SqlParameter("@SFZH", SqlDbType.VarChar, 20), //身份证号 new SqlParameter("@HJDZ", SqlDbType.VarChar,100),//详细地址 new SqlParameter("@XZQ",SqlDbType.VarChar,10),//行政区 new SqlParameter("@strGZDW",SqlDbType.VarChar,60), //工作单位 new SqlParameter("@XB",SqlDbType.VarChar,10),//性别 new SqlParameter("@CZLX",SqlDbType.Char,1), //户籍类型 new SqlParameter("@PageIndex",SqlDbType.Int,4), new SqlParameter("@PageSize",SqlDbType.Int ,4) }; parameters[0].Value = strJMJKDAH; //居民健康档案号 if (lag == "1") { parameters[1].Value = strYLJGBH + "%";//医疗机构号 } else { parameters[1].Value = strYLJGBH;//医疗机构号 } parameters[2].Value = strXM + "%"; //姓名 parameters[3].Value = CSRQ;//出生日期 parameters[4].Value = CSRQE;//出生日期搜索结束时间 parameters[5].Value = BeginDate; //起始日期 parameters[6].Value = EndDate;//结束日期 parameters[7].Value = YHBH;//用户编号 parameters[8].Value = SFZH;//身份证号码 parameters[9].Value = "%" + strHJDZ + "%";//详细地址 parameters[10].Value = strXZQ + "%"; parameters[11].Value = strGZDW; //工作单位 parameters[12].Value = strXb; parameters[13].Value = CZLX; //户籍类型 parameters[14].Value = PageIndex; parameters[15].Value = PageSize; strsql2005.Append(sqlwhere.ToString()); sqlcount.Append(sqlwhere.ToString()); object intcount = DbHelperSQL.GetSingle(sqlcount.ToString(), parameters); if (intcount == null) RecordCount = 0; else RecordCount = int.Parse(intcount.ToString()); return DbHelperSQL.Query(strSql.ToString(), PageSize, PageIndex, parameters);
解决方案
create or replace procedure name(参数)
as
.....