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 ONGOSET QUOTED_IDENTIFIER ONGO /* ----------------------------------------------------------------------------- 《根据入井时间获取相应的班次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 ONGOSET QUOTED_IDENTIFIER ONGO/*----------------------------------------------------------------------------- 《根据入井时间获取相应的班次ID》-----------------------------------------------------------------------------参数： 1.@InWellTime 入井时间返回值：int型 班次ID----------------------------------------------------------------------------- Written by -----------------------------------------------------------------------------*/create function [dbo].[GetClassIDWithName](@InWellTime varchar(50))returns int asbegin declare @returnValue int set @returnValue=0 select @returnValue=classID from v_Class where 时间段名称 =@InWellTime return @returnValueend GO