--按状态查询
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[StatusType]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[StatusType]
GO
CREATE PROCEDURE [dbo].[StatusType]
(@statustype nvarchar(50),@startdate nvarchar(200),@enddate nvarchar(200))
AS
declare @pronum int
CREATE TABLE #t(Status nvarchar(50),ProNum nvarchar(50))
set @pronum = (select count(*) from employee where Status='在职' and factorytime >= @startdate and factorytime<= isnull(@enddate,getdate()))
INSERT INTO #t VALUES ('在职',@pronum)
set @pronum = (select count(*) from employee where Status='离职' and factorytime >= @startdate and factorytime<= isnull(@enddate,getdate()))
INSERT INTO #t VALUES ('离职',@pronum)
set @pronum = (select count(*) from employee where Status='辞退' and factorytime >= @startdate and factorytime<= isnull(@enddate,getdate()))
INSERT INTO #t VALUES ('辞退',@pronum)
IF (@statustype ='')
select * from #t where 1=1
ELSE
select * from #t where Status =@statustype
GO
--按厂区查询
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PlantType]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[PlantType]
GO
CREATE PROCEDURE [dbo].[PlantType]
(@planttype nvarchar(50),@startdate nvarchar(200),@enddate nvarchar(200))
AS
declare @pronum int
select Id as PlantId,Name,(select count(*) from employee where PlantId = Plant.Id) as ProNum into #t from Plant
IF (@planttype ='')
select * from #t where 1=1
ELSE
select * from #t where PlantId =@planttype
GO
exec [dbo].[PlantType] '','1990-01-01','2080-08-08'
//数据库连接字符窜
SqlConnection conn = new SqlConnection(DBHelper.strCon);
string proc_name = "PlantType";
SqlCommand cmd = new SqlCommand(proc_name, conn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter sp = cmd.Parameters.Add("@planttype", SqlDbType.NVarChar, 50);//性别
sp.Value = "";
if (ddlPlantType.SelectedValue != "0")
{
sp.Value = ddlPlantType.SelectedValue;
}
sp.Direction = ParameterDirection.Input;
sp = cmd.Parameters.Add("@startdate", SqlDbType.NVarChar, 50);
sp.Value = "1990-01-01";
if (txtStartDate.Text != string.Empty)
{
sp.Value = txtStartDate.Text;
}
sp.Direction = ParameterDirection.Input;
sp = cmd.Parameters.Add("@enddate", SqlDbType.NVarChar, 50);
sp.Value = "2020-11-01";
if (txtEndDate.Text != string.Empty)
{
sp.Value = txtEndDate.Text;
}
sp.Direction = ParameterDirection.Input;
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "PlantType");
this.GvData.DataSource = ds;
this.GvData.DataBind();