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 [d

--按状态查询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),@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.@R_502_5983@;        sp = cmd.Parameters.Add("@startdate",50);        sp.Value = "1990-01-01";        if (txtStartDate.Text != string.Empty)        {            sp.Value = txtStartDate.Text;        }        sp.Direction = ParameterDirection.@R_502_5983@;        sp = cmd.Parameters.Add("@enddate",50);        sp.Value = "2020-11-01";        if (txtEndDate.Text != string.Empty)        {            sp.Value = txtEndDate.Text;        }        sp.Direction = ParameterDirection.@R_502_5983@;        sqlDataAdapter da = new sqlDataAdapter(cmd);        DataSet ds = new DataSet();        da.Fill(ds,"PlantType");        this.GvData.DataSource = ds;        this.GvData.DataBind();

总结

以上是内存溢出为你收集整理的sqlserver 临时表 *** 作全部内容，希望文章能够帮你解决sqlserver 临时表 *** 作所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。