函数如下
CREATE FUNCTION StockBalance_AmountIn(@SortID int)
RETURNS numeric(18,4)
AS
BEGIN
Declare @dblReturn numeric(18,4)
Select @dblReturn = SUM(AmountCurrentMonthIn) From AT_Materiel_StockBalance Where SortID = @SortID
Return (@dblReturn)
END
调用方法
select dbo.StockBalance_AmountIn(2523)
这里头需要加dbo
=========================
表格变量
USE [mbmxdb]
GO
/****** Object: UserDefinedFunction [dbo].[uf_GetResearcherTable] Script Date: 04/23/2010 09:50:47 ******/
SET QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[uf_GetResearcherTable](@UserID varchar(50))
returns @t table(col varchar(50))
as
begin
declare @temp table (ID int IDENTITY (1, 1) NOT NULL , OrderRight varchar(10),Researcher varchar(50))
declare @ResearcherInfo varchar(50)
DECLARE @ID int
SET @ResearcherInfo=''
insert into @temp(OrderRight,Researcher)
( SELECT
RI.OrderRight as OrderRight,
(CASE WHEN RI.IsAnalyst=1 THEN '分析师'+':'+UserName
ELSE '联系人'+':'+UserName
END) as Researcher
FROM kinghing..vemployeeinfo_yjs Vyjs Inner Join
(
Select
DISTINCT MR.RelatedID,MR.OrderRight,MI.IsAnalyst,MR.Type,MR.NameFontStyle,MR.PhoneFontStyle,MR.EmailFontStyle,MR.Separator,Mi.CertificateID
from MBMXResearcherInfo MI Inner Join
(Select RelatedID,OrderRight,Type,NameFontStyle,PhoneFontStyle,EmailFontStyle,Separator From MBMXResearcherInfoReferNew where UserID=@UserID) MR
On MI.UserID=MR.RelatedID
) RI
On Vyjs.UserID=RI.RelatedID where Vyjs.userstatus <4
)
WHILE ((select count(*) from @temp)>0)
BEGIN
select top 1 @ResearcherInfo=@ResearcherInfo+Researcher+';',@ID=ID from @temp order by OrderRight
DELETE FROM @temp WHERE ID=@ID
END
SET @ResearcherInfo= substring(@ResearcherInfo,1,len(@ResearcherInfo)-1)
insert @t(col) values (@ResearcherInfo)
return
end
调用 select col from kinghing..f_split(@usernameID,';')