/ ************************************************** ************************ Function name: getClassName (@ classid, @ classtype) parameter: @ClassID category ID number, @ ClassType, category Type 0: Language 1: Total Category, 2: Big Category, 3: Subcord, 4: Country 5: Province 6: City County Back: @classname Category NVARCHAR (100) Power By Adpostcreate Time: March 24, 2004 Day 14:16:16 ************************************************************* ************************************** / CREATE FUNCTION GETCLASSNAME (@classid as int, @classtype as int) returns nvarchar (100) asbegin 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_coun try 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 @classnameend
Application of SQL Custom Function in SQL Query:
I post a self-defined SQL view to everyone.
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.COUNTRYD, 4) As countrynam e, 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.
