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

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

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
GOSET 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]
GOUSE [BW_Contrast]
GO/****** Object: UserDefinedFunction [dbo].[GetClassIDWithName] Script Date: 01/15/2015 14:31:39 ******/
SET ANSI_NULLS ON
GOSET 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