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

本文详细介绍了在C#中如何调用SQL的标量值函数和表值函数,包括具体语法和示例代码,同时展示了如何创建SQL自定义函数,如获取特定时间的班次ID和分析班次数据。

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

 

转载于:https://www.cnblogs.com/xiaochun126/p/4226296.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值