SQL函数调用

 

函数如下

 

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,';')