存储过程参数加入sql语句

[wuxiaoqqqq]

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) ;
END

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
)
)

这是我写的储存过程，我想把@whereType加到这个sql语句，请问有什么好法子没？

[wuxiaoqqqq]

就是在我打****的地方，我想加这个参数进去。
大家帮帮忙，我搞了半天了。
用set @s='前面' +@whereType+ '后面'
的话，我后面的@companyID就不能用了。
要把@companyID提出来的话，它是int型的，说不能直接加。
大家给个解决方法咯。

[nextflying]

cast(@companyID as varchar(100)) 加上

[pt1314917]

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   
))') 

[nextflying]

动态SQL 先进行strSQl编写 ， 然后 Exec（strSql）

[wuxiaoqqqq]

这样不行啊，下面的@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   
) 
) ')

[ojuju10]

ltrim(@companyID) 可以转换为字符型

[wuxiaoqqqq]

行了，搞完了。
把@companyid的类型转了一下。