SQL自定义函数的应用

王朝mssql·作者佚名  2006-12-16
窄屏简体版  字體: |||超大  

/**************************************************************************

函数名:GetClassName(@ClassID,@ClassType)

参数:@ClassID 类别的ID号,@ClassType,类别的种类 0:语言1:总分类,2:大类别,3:子类别 ,4:国家5:省份6:市县

返回:@ClassName类别的名称 nvarchar(100)

POWER BY ADPOST

create time : 2004年3月24日 14:16:16

*************************************************************************/

CREATE FUNCTION GetClassName(@ClassID as int, @ClassType as int)

RETURNS nvarchar(100) as

begin

declare @ClassName as nvarchar(100)

set @ClassName = ''

if(@ClassType = 0)

begin

SELECT @ClassName = LngName FROM LXBIZ_Language WHERE (LngTypeID = @ClassID)

end

if(@ClassType = 1)

begin

SELECT @ClassName = CategoryName FROM LXBIZ_Category WHERE (CategoryID = @ClassID)

end

if(@ClassType = 2)

begin

SELECT @ClassName = ClassName FROM LXBIZ_BigClass WHERE (ClassID = @ClassID)

end

if(@ClassType = 3)

begin

SELECT @ClassName = SubClassName FROM LXBIZ_SubClass WHERE (SubClassID = @ClassID)

end

if(@ClassType = 4)

begin

SELECT @ClassName = CountryName FROM LXBIZ_Country WHERE (CountryID = @ClassID)

end

if(@ClassType = 5)

begin

SELECT @ClassName = ProvinceName FROM LXBIZ_Province WHERE (ProvinceID = @ClassID)

end

if(@ClassType = 6)

begin

SELECT @ClassName = CityName FROM LXBIZ_City WHERE (CityID = @ClassID)

end

return @ClassName

end

SQL自定义函数在SQL查询中的应用:

我帖一个我自定义的SQL视图给大家看一下

SELECT dbo.LXBIZ_AccoutInfo.AccoutID, dbo.LXBIZ_AccoutInfo.AccoutName,

dbo.LXBIZ_AccoutInfo.AccoutPassWord, dbo.LXBIZ_AccoutInfo.SafeQuestion,

dbo.LXBIZ_AccoutInfo.SafeAnswer, dbo.LXBIZ_AccoutInfo.AccoutEmail,

dbo.LXBIZ_AccoutInfo.AccoutFlag, dbo.LXBIZ_AccoutInfo.AccoutLevel,

dbo.LXBIZ_AccoutInfo.AccountExpireTime, dbo.LXBIZ_AccoutInfo.AccountMoney,

dbo.LXBIZ_AccoutInfo.LoginCount, dbo.LXBIZ_AccoutInfo.LastLoginIP,

dbo.LXBIZ_AccoutInfo.LastLoginTime, dbo.LXBIZ_AccoutInfo.RegTime,

dbo.LXBIZ_EnterpriseInfo.EnterpriseID, dbo.LXBIZ_EnterpriseInfo.CompanyFileUrl,

dbo.LXBIZ_EnterpriseInfo.ClassID,

dbo.GetClassName(dbo.LXBIZ_EnterpriseInfo.ClassID, 2) AS ClassName,

dbo.LXBIZ_EnterpriseInfo.ModeTypeID, dbo.LXBIZ_EnterpriseInfo.SellKeyword,

dbo.LXBIZ_EnterpriseInfo.BuyKeyWord, dbo.LXBIZ_EnterpriseInfo.CompanyName,

dbo.LXBIZ_EnterpriseInfo.CountryID,

dbo.GetClassName(dbo.LXBIZ_EnterpriseInfo.CountryID, 4) AS CountryName,

dbo.LXBIZ_EnterpriseInfo.ProvinceID, dbo.LXBIZ_EnterpriseInfo.CityID,

dbo.LXBIZ_EnterpriseInfo.CompanyAddress, dbo.LXBIZ_EnterpriseInfo.CompanyUrl,

dbo.LXBIZ_EnterpriseInfo.ContactName, dbo.LXBIZ_EnterpriseInfo.JobTitle,

dbo.LXBIZ_EnterpriseInfo.ContactSex, dbo.LXBIZ_EnterpriseInfo.ContactTel,

dbo.LXBIZ_EnterpriseInfo.ContactFax, dbo.LXBIZ_EnterpriseInfo.ContactMobile,

dbo.LXBIZ_EnterpriseInfo.PostCode, dbo.LXBIZ_EnterpriseInfo.BankName,

dbo.LXBIZ_EnterpriseInfo.BankNumber, dbo.LXBIZ_EnterpriseInfo.Turnover,

dbo.LXBIZ_EnterpriseInfo.EmployersNum, dbo.LXBIZ_EnterpriseInfo.CompnayLICD,

dbo.LXBIZ_EnterpriseInfo.CompanyHits,

dbo.GetClassName(dbo.LXBIZ_EnterpriseInfo.LngTypeID, 0) AS LngName, <这是取出语言类别的名称,调用以上定义的自定义函数>

dbo.LXBIZ_EnterpriseInfo.IssueTime, dbo.LXBIZ_EnterpriseInfo.Corporate

FROM dbo.LXBIZ_EnterpriseInfo INNER JOIN

dbo.LXBIZ_AccoutInfo ON

dbo.LXBIZ_EnterpriseInfo.AccoutID = dbo.LXBIZ_AccoutInfo.AccoutID

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航