需要使用innerquery创建一个存储过程 [英] need to create one stored procedure with innerquery

问题描述

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

i need to create a stored procedure for search function.the search is done on 5 criterias firstname,lastname,topfunctionname,subfunctionname,region,country,site...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 i desnt contain country id and region id i cant 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....

推荐答案

您好，您必须在sql中编写一个动态查询.您可以使用if条件，并可以创建变量并在查询中使用它们.要进行动态查询，您可以从Google获得帮助.有很多解决方案.只有我告诉过流程.

Hi, You have to write a dynamic query in sql. you can use if condition and can make your variables and use these in your query. For making dynamic query, you can help from google. There are many solutions for this. Only i told process.

这篇关于需要使用innerquery创建一个存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！