IDC资讯中心
函数名: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
