sql自定义函数及C#中调用

浏览数：33 / 时间：2015年06月12日

1、在C#中调用sql自定义函数

1.1 标量值函数

sql语句调用 select dbo.GetClassIDWithName(1)

string strSql = string.Format("select dbo.GetClassIDWithName(‘{0}‘)",dtTime);
DataTable dt = DB_Contrast.DB.OleDbHelper.GetDataTable(strSql);

1.2 表值函数

sql语句调用 select * from GetAnalysis(‘2015-1-15‘,1)

string strSql = string.Format("select * from dbo.GetAnalysis(‘{0}‘,{1}) where 部门=‘{2}‘ ",dtTime, classid,"开发");
DataSet ds = DB_Contrast.DB.OleDbHelper.GetDataSet(strSql);

2、表值函数，

内层select获取不重复的记录

外层按照部门进行分组

USE [BW_Contrast]
GO
/****** Object:  UserDefinedFunction [dbo].[GetAnalysis]    Script Date: 01/15/2015 13:09:17 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N‘[dbo].[GetAnalysis]‘) AND type in (N‘FN‘, N‘IF‘, N‘TF‘, N‘FS‘, N‘FT‘))
DROP FUNCTION [dbo].[GetAnalysis]
GO

/****** Object:  UserDefinedFunction [dbo].[GetAnalysis]    Script Date: 01/15/2015 13:09:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
   /*
   -----------------------------------------------------------------------------
  《根据入井时间获取相应的班次ID》
   -----------------------------------------------------------------------------
   参数：
　　　1、@classdate   班次日期

        2、@classid 班次ID

         返回值：table
   -----------------------------------------------------------------------------
   Written by 
   -----------------------------------------------------------------------------
   */

CREATE function [dbo].[GetAnalysis](@classdate datetime,@classid int)
returns table as 
  return (
--declare @classdate datetime,@classid int
--set @classid=2
--set @classdate=‘2015-1-14‘
select 
    case when(grouping(a.部门)=1) then ‘合计‘ else a.部门 end as 部门
   ,(select top 1 ID from dbo.v_Dept where 部门名称=a.部门) as deptid
   ,Sum(case when dt_RealTime is not null and myclassid=@classid  then 1 else 0 end ) as  派班人数
   ,Sum(case when dt_GetTime is not null and myclassid=@classid  then 1 else 0 end ) as  领灯人数
   ,Sum(case when dtInWellTime is not null and myclassid=@classid  then 1 else 0 end ) as  下井人数
   ,Sum(case when dt_OutWellTime is not null and myclassid=@classid  then 1 else 0 end ) as  上井人数
   ,Sum(case when dt_ReturnTime is not null and myclassid=@classid  then 1 else 0 end ) as  还灯人数
 from
(
    select 
        部门,deptid,myclassdate,myclassid,mypersonid
        ,min(dt_RealTime) as  dt_RealTime
        ,min(dt_GetTime) as dt_GetTime
        ,min(dtInWellTime) as dtInWellTime
        ,min(dt_OutWellTime) as dt_OutWellTime
        ,min(dt_ReturnTime) as dt_ReturnTime
    from v_ALL_NEW
    where myclassdate=@classdate and myclassid=@classid
    group by 部门,myclassdate,myclassid,mypersonid,deptid
)a    
where  myclassdate=@classdate and myclassid=@classid and 部门 is not null 
group by 部门
with rollup
)

GO

3、标量值函数

USE [BW_Contrast]
GO

/****** Object:  UserDefinedFunction [dbo].[GetClassIDWithName]    Script Date: 01/15/2015 14:31:39 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N‘[dbo].[GetClassIDWithName]‘) AND type in (N‘FN‘, N‘IF‘, N‘TF‘, N‘FS‘, N‘FT‘))
DROP FUNCTION [dbo].[GetClassIDWithName]
GO

USE [BW_Contrast]
GO

/****** Object:  UserDefinedFunction [dbo].[GetClassIDWithName]    Script Date: 01/15/2015 14:31:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*
-----------------------------------------------------------------------------
                              《根据入井时间获取相应的班次ID》
-----------------------------------------------------------------------------
参数：
　　　1.@InWellTime   入井时间

返回值：int型 班次ID
-----------------------------------------------------------------------------
                                 Written by 
-----------------------------------------------------------------------------
*/
create    function [dbo].[GetClassIDWithName](@InWellTime varchar(50))
returns int as
begin
  declare @returnValue int
  set @returnValue=0 
   select @returnValue=classID from v_Class where 时间段名称 =@InWellTime
  return @returnValue
end 

GO