DECLARE @companyType nvarchar(200);
DECLARE @whereType NVARCHAR(200);
declare @id varchar(300),@m int,@n int;
DECLARE @companyID int;
set @companyID=117
set @companyType='AwardingAgency,CMatRisk,Oversight';
SET @companyType = @companyType+',';
SET @whereType = ' (0=1 ';
set @m=CHARINDEX(',',@companyType)
set @n=0
WHILE @m>0
BEGIN
set @id=substring(@companyType,@n,@m-@n)
IF( @id='AwardingAgency')
SET @whereType =@whereType + ' OR a.AllowAwardingAgencyView = 1'
ELSE IF(@id='CMatRisk')
SET @whereType =@whereType + ' OR a.AllowCMatRiskView=1';
ELSE IF(@id='Oversight')
SET @whereType =@whereType + ' OR a.AllowOversightAgencyView=1';
ELSE IF(@id='Contractor')
SET @whereType =@whereType + ' OR a.AllowContractorView=1';
ELSE IF(@id='ApplicationAdministrator')
SET @whereType=@whereType + ' OR a.AllowApplicationAdministratorView=1';
set @n=@m+1 ;
set @m=CHARINDEX(',',@companyType,@n) ;
ENDselect distinct a.TabName from Tabs a inner join Tabs b on b.TabID=a.ParentTab
where ******+@whereType+****** and b.TabName in
(
SELECT distinct ModuleName FROM AgencyModules
WHERE CompanyID in
(
select distinct a.AwardingAgencyCompanyID
from Projects a inner join CompanyProject b on a.ProjectID=b.ProjectID
where A.AwardingAgencyCompanyID=@companyID OR A. OwnerCompanyID=@companyID
OR A.PrimeContractorCompanyID=@companyID OR B.CompanyID=@companyID
)
)这是我写的储存过程,我想把@whereType加到这个sql语句,请问有什么好法子没?
DECLARE @whereType NVARCHAR(200);
declare @id varchar(300),@m int,@n int;
DECLARE @companyID int;
set @companyID=117
set @companyType='AwardingAgency,CMatRisk,Oversight';
SET @companyType = @companyType+',';
SET @whereType = ' (0=1 ';
set @m=CHARINDEX(',',@companyType)
set @n=0
WHILE @m>0
BEGIN
set @id=substring(@companyType,@n,@m-@n)
IF( @id='AwardingAgency')
SET @whereType =@whereType + ' OR a.AllowAwardingAgencyView = 1'
ELSE IF(@id='CMatRisk')
SET @whereType =@whereType + ' OR a.AllowCMatRiskView=1';
ELSE IF(@id='Oversight')
SET @whereType =@whereType + ' OR a.AllowOversightAgencyView=1';
ELSE IF(@id='Contractor')
SET @whereType =@whereType + ' OR a.AllowContractorView=1';
ELSE IF(@id='ApplicationAdministrator')
SET @whereType=@whereType + ' OR a.AllowApplicationAdministratorView=1';
set @n=@m+1 ;
set @m=CHARINDEX(',',@companyType,@n) ;
ENDselect distinct a.TabName from Tabs a inner join Tabs b on b.TabID=a.ParentTab
where ******+@whereType+****** and b.TabName in
(
SELECT distinct ModuleName FROM AgencyModules
WHERE CompanyID in
(
select distinct a.AwardingAgencyCompanyID
from Projects a inner join CompanyProject b on a.ProjectID=b.ProjectID
where A.AwardingAgencyCompanyID=@companyID OR A. OwnerCompanyID=@companyID
OR A.PrimeContractorCompanyID=@companyID OR B.CompanyID=@companyID
)
)这是我写的储存过程,我想把@whereType加到这个sql语句,请问有什么好法子没?
大家帮帮忙,我搞了半天了。
用set @s='前面' +@whereType+ '后面'
的话,我后面的@companyID就不能用了。
要把@companyID提出来的话,它是int型的,说不能直接加。
大家给个解决方法咯。
select distinct a.TabName from Tabs a inner join Tabs b on b.TabID=a.ParentTab
where ******+@whereType+****** and b.TabName in
(
SELECT distinct ModuleName FROM AgencyModules
WHERE CompanyID in
(
select distinct a.AwardingAgencyCompanyID
from Projects a inner join CompanyProject b on a.ProjectID=b.ProjectID
where A.AwardingAgencyCompanyID=@companyID OR A. OwnerCompanyID=@companyID
OR A.PrimeContractorCompanyID=@companyID OR B.CompanyID=@companyID
)
)
---改为:
exec('select distinct a.TabName from Tabs a inner join Tabs b on b.TabID=a.ParentTab
where '+@whereType+' and b.TabName in
(
SELECT distinct ModuleName FROM AgencyModules
WHERE CompanyID in
(
select distinct a.AwardingAgencyCompanyID
from Projects a inner join CompanyProject b on a.ProjectID=b.ProjectID
where A.AwardingAgencyCompanyID=@companyID OR A. OwnerCompanyID=@companyID
OR A.PrimeContractorCompanyID=@companyID OR B.CompanyID=@companyID
))')
where '+@whereType+' and b.TabName in
(
SELECT distinct ModuleName FROM AgencyModules
WHERE CompanyID in
(
select distinct a.AwardingAgencyCompanyID
from Projects a inner join CompanyProject b on a.ProjectID=b.ProjectID
where A.AwardingAgencyCompanyID=@companyID OR A. OwnerCompanyID=@companyID
OR A.PrimeContractorCompanyID=@companyID OR B.CompanyID=@companyID
)
) ')
ltrim(@companyID) 可以转换为字符型
把@companyid的类型转了一下。