function function Name: GetClassName (@ClassID, @ClassType)
Parameters: ID number of @ClassID category, @ClassType, category 0: Language 1: Total classification, 2: Large category, 3: Subcategory, 4: Country 5: Province 6: City and County
Returns: The name of the @ClassName category nvarchar (100)
Power by Adpost
Create time:2004 year March 24 14:16:16
*************************************************************************/
CREATE FUNCTION getclassname (@ClassID as int, @ClassType as int)
RETURNS nvarchar (m) 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 Custom function application in SQL query:
I post a custom SQL view for you to see
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, < This is to remove the name of the language category and call the Custom function defined above >
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
