我的存储过程出现问题 [英] problem with my stored procedure

问题描述

我需要创建一个用于搜索功能的存储过程.搜索是根据存储过程中定义的11个标准进行的，其中包括名字，姓氏，顶级功能名称，子功能名称，区域，国家/地区....用户可以选择任何一个这些字段来自不同的表，这些表的ID在主表tbluserprofile中指定.我编写的过程是

i need to create a stored procedure for search function.the search is done on 11 criterias firstname,lastname,topfunctionname,subfunctionname,region,country,site...which defined in the stored procedure..the user can select any of this criteria for searching.these fields are from different tables whose id is specified in the master table tbluserprofile.the procedure i wrote is

ALTER Procedure [dbo].[SP_Search]
( 
@firstName nvarchar(50) = null,
@lastName nvarchar(50) = null,
@region nvarchar(50),
@country nvarchar(50),
@site nvarchar(50),
@topfunction nvarchar(50),
@subfunction nvarchar(50),
@status nvarchar(50),
@role nvarchar(50),
@channel nvarchar(50),
@kmlevel int
) 
AS 
BEGIN
 
If @firstName is not null and Len(@firstName )=0 Set @firstName = null
If @lastName is not null and Len(@lastName )=0 Set @lastName = null
If @region  is not null and Len(@region  )=0 Set @region   = null
If @country  is not null and Len(@country  )=0 Set @country   = null
If @site is not null and Len(@site )=0 Set @site  = null
If @topfunction  is not null and Len(@topfunction )=0 Set @topfunction   = null
If @subfunction  is not null and Len(@subfunction  )=0 Set @subfunction   = null
If @status  is not null and Len(@status  )=0 Set @status   = null
If @role  is not null and Len(@role  )=0 Set @role   = null
If @channel  is not null and Len(@channel  )=0 Set @channel   = null
If @kmlevel  is not null and Len(@kmlevel  )=0 Set @kmlevel   = null
 
Select firstname,lastname,regionfullname ,countryname sitename,topfunctionname,subfunctionname,lmsrolename,channelname,statusname,kmlevel 
from tblUserProfile up,tblChannel tc ,tblLMSRole tr,tblSite ts,tblSubFunction tsf,tblTopFunction ttf,tblStatus sta,
tblRegion,tblCountry  
Where 
tc.channelid =up.channelid and tr.lmsroleid =up.lmsroleid 
and ts.siteid =up.siteid and tsf.subfunctionid =up.subfunctionid
and ttf.topfunctionid =up.topfunctionid and sta.statusid =up.statusid    
and @firstName is null or firstName Like @firstName
and @lastName is null or lastName Like @lastName
and @region  is null or regionfullname = @region
and @country   is null or countryname = @country   
and @site  is null or sitename = @site
and @topfunction is null or topfunctionname = @topfunction 
and @subfunction is null or subfunctionname = @subfunction 
and @status is null or statusname = @status 
and @role is null or lmsrolename = @role  
and @channel  is null or channelname = @channel 
and @kmlevel  is null or kmlevel = @kmlevel  
END 

这里的问题是主表tbluserprofile不包含区域ID和国家ID.它仅包含站点ID.所以问题在于过滤部分.即

here the problem is the master table tbluserprofile does not contain region id and counrty id.it contains only the site id.so problem is with the filtering section .that is

Select firstname,lastname,regionfullname ,countryname sitename,topfunctionname,subfunctionname,lmsrolename,channelname,statusname,kmlevel 
from tblUserProfile up,tblChannel tc ,tblLMSRole tr,tblSite ts,tblSubFunction tsf,tblTopFunction ttf,tblStatus sta,
tblRegion,tblCountry  
Where 
tc.channelid =up.channelid and tr.lmsroleid =up.lmsroleid 
and ts.siteid =up.siteid and tsf.subfunctionid =up.subfunctionid
and ttf.topfunctionid =up.topfunctionid and sta.statusid =up.statusid    

由于它不包含国家/地区ID和区域ID，因此我无法为此编写过滤条件.在这里，我认为唯一的解决方案是使用innerquery从站点ID获取所有区域和国家/地区，但我不这样做知道怎么写...任何人都可以为我修改以上过程.... sitetable包含站点ID，站点名称和国家/地区ID.区域表由区域ID区域名称和国家/地区ID组成......

since it doesn''t contain country id and region id, i can''t write the filtering condition for that..here i think the only solution is to get all the regions and countries from the site id using innerquery.but i dont know how to write it...can anyone pllss modify the above procedure for me.......sitetable consist of site id site name and country id. region table consist of regionid regionname and country id.... .

推荐答案

阿马尔
我在下面假设站点表将包含区域信息，而不仅仅是国家信息.

Hi Amal
I assumed below that the site table would contain region information, not just country information.

Select 
   firstname,
   lastname,
   regionfullname,
   countryname, 
   sitename,
   topfunctionname,
   subfunctionname,
   lmsrolename,
   channelname,
   statusname,
   kmlevel 
from 
   tblUserProfile up,
   tblChannel tc ,
   tblLMSRole tr,
   tblSite ts,
   tblSubFunction tsf,
   tblTopFunction ttf,
   tblStatus sta,
   tblRegion tReg,
   tblCountry tCountry 
Where 
  tc.channelid = up.channelid and 
  tr.lmsroleid = up.lmsroleid and 
  ts.siteid = up.siteid and 
  tsf.subfunctionid = up.subfunctionid and 
  ttf.topfunctionid = up.topfunctionid and 
  sta.statusid = up.statusid and
  ts.countryId = tCountry.countryId and
  ts.RegionId  = tReg.RegionId  -- Assumption here that the site table will contain region information too

这篇关于我的存储过程出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！