SQL存储过程中的代码:
存储过程查询出两个表
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[pro_C#]
@intYear VarChar(4),
@intSeason VarChar(1)
AS
BEGIN
DECLARE @strSQLC VarChar(2000)
DECLARE @strSQLS VarChar(2000)
DECLARE @strSQL VarChar(2000)
DECLARE @strSQL1 VarChar(200)
DECLARE @strStartDatetime VarChar(30)
DECLARE @strEndDatetime VarChar(30)
DECLARE @tblName VarChar(30)
SET @tblName='tbl'+@intYear
IF(@intSeason='1')
BEGIN
SET @strStartDatetime=@intYear+'-1-1'
SET @strEndDatetime=@intYear+'-3-31'
end
IF(@intSeason='2')
BEGIN
SET @strStartDatetime=@intYear+'-4-1'
SET @strEndDatetime=@intYear+'-6-30'
end
IF(@intSeason='3')
BEGIN
SET @strStartDatetime=@intYear+'-7-1'
SET @strEndDatetime=@intYear+'-10-31'
end
IF(@intSeason='4')
BEGIN
SET @strStartDatetime=@intYear+'-10-1'
SET @strEndDatetime=@intYear+'-12-31'
end
set @strSQL='sum(case when casetype=1 then 1 else 0 end) AS BJtotal,
sum(case when casetype=1 and bigclass=1 then 1 else 0 end) AS BJpublic,
sum(case when casetype=1 and bigclass=2 then 1 else 0 end) AS BJtraffic,
sum(case when casetype=1 and bigclass=3 then 1 else 0 end) AS BJsurround,
sum(case when casetype=1 and bigclass=4 then 1 else 0 end) AS BJgreen,
sum(case when casetype=1 and bigclass=5 then 1 else 0 end) AS BJhouse,
sum(case when casetype=2 then 1 else 0 end) AS SJtotal,
sum(case when casetype=2 and bigclass=1 then 1 else 0 end) AS SJsurround,
sum(case when casetype=2 and bigclass=2 then 1 else 0 end) AS SJadvertise,
sum(case when casetype=2 and bigclass=3 then 1 else 0 end) AS SJmanage,
sum(case when casetype=2 and bigclass=4 then 1 else 0 end) AS SJurgent,
sum(case when casetype=2 and bigclass=5 then 1 else 0 end) AS SJstreet
INTO '
set @strSQL1= 'set val=((BJpublic*0.24+BJtraffic*0.21+BJsurround*0.03+BJgreen*0.51+BJhouse*0.01)*0.05+(SJsurround*0.33+SJadvertise*0.22+SJmanage*0.03+SJurgent*0.01+SJstreet*0.41)*0.95)/area;'
set @strSQLC='SELECT CommunityCode,CommunityName,STName,'+@strSQL+'#tblRC
FROM '+@tblName+'
WHERE UploadTime BETWEEN '''+@strStartDatetime+''' AND '''+@strEndDatetime+'''
GROUP BY CommunityCode,CommunityName,STName order by STName asc;
alter table #tblRC add area float,val float;
update #tblRC set #tblRC.area=community.area from community where #tblRC.CommunityName =community.SQName;
update #tblRC '+@strSQL1;
EXEC(@strSQLC+' SELECT * FROM #tblRC')
set @strSQLS='SELECT STCode,STName,'+@strSQL+'#tblRS
FROM '+@tblName+'
WHERE UploadTime BETWEEN '''+@strStartDatetime+''' AND '''+@strEndDatetime+'''
GROUP BY STCode,STName order by STName asc;
alter table #tblRS add area float,val float;
update #tblRS set #tblRS.area=street.area from street where #tblRS.STName =street.JDName;
update #tblRS '+@strSQL1;
EXEC(@strSQLS+' SELECT * FROM #tblRS')
END
C#代码:
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
SqlConnection conn;
SqlCommand comm;
SqlDataAdapter sda;
DataSet myDataSet;
private void Form1_Load(object sender, EventArgs e)
{
conn = new SqlConnection("server=10.19.1.55;uid=sa;pwd=123;database=08_09cgywdb;Connection TimeOut=2");
conn.Open();
//调用存储过程
comm = new SqlCommand();
comm.Connection = conn;
comm.CommandText = "pro_C#";//存储过程的名字
comm.CommandType = CommandType.StoredProcedure;
IDataParameter[] parameters =
{
new SqlParameter("@intYear", SqlDbType.VarChar,4) , //存储过程中参数的名字
new SqlParameter("@intSeason", SqlDbType.VarChar,1) //存储过程中参数的名字
};
parameters[0].Value = "2009";
parameters[1].Value = "2";
comm.Parameters.Add(parameters[0]);
comm.Parameters.Add(parameters[1]);
sda = new SqlDataAdapter();
sda.SelectCommand = comm;
myDataSet = new DataSet();
sda.Fill(myDataSet);
conn.Close();
dataGridView1.DataSource = myDataSet.Tables[0];
dataGridView2.DataSource = myDataSet.Tables[1];
}
}