sqlserver 临时表操作

--按状态查询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   GOexec [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();