sqlserver 临时表操作

最新推荐文章于 2025-06-16 15:07:56 发布

原创最新推荐文章于 2025-06-16 15:07:56 发布 · 906 阅读

0 ·

CC 4.0 BY-SA版权

ASP.NET 同时被 2 个专栏收录

19 篇文章

订阅专栏

SqlServer 2005+

6 篇文章

订阅专栏

本文介绍两个SQL Server存储过程：按员工状态查询与按厂区查询。通过动态SQL创建临时表存储查询结果，并根据输入参数筛选输出数据。展示了如何使用C#连接数据库并调用存储过程。

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