动态执行才有效,比如传入字符串'a,b,c,d' create procedure protest @s varchar(10) as declare @sql varchar(4000) set @sql='select * from tb where col1 like ''%'+@s+'%''' exec(@sql) go declare @s varchar(10) set @s='a,b,c,d' excute protest @s
我的意思是引号中别再用引号括起来,比如"select ','"这样. 你举个例子.
string query = "select A.projectname,A.projectid,A.Xi,B.CLx,B.UCLx,B.LCLx from(select projectname,value as Xi,projectid from TBLPAS_PRJ_TARGET where type = '进度控制' and projectid in (@projectid)) as A,(select avg (CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) as CLx,avg(CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) + stdev(CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) as UCLx,avg(CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) - stdev(CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) as LCLx from TBLPAS_PRJ_TARGET where type = '进度控制' and projectid in (@projectid)) as B "; SqlConnection conn = new SqlConnection("User ID=sa;Initial Catalog=eprocms;Data Source=localhost"); conn.Open(); SqlCommand command = new SqlCommand(); command.CommandText = query; command.Connection = conn; SqlParameter sp1 = new SqlParameter("@projectid",SqlDbType.Char,200); sp1.Direction = ParameterDirection.Input; command.Parameters.Add(sp1); sp1.Value = "1001" + "," + "1002";//这里的逗号不用引号怎么传呢?
这样declare @projectid varchar(100) set @projectid = '1001,1002' select * from 表 where charindex(','+rtrim(projectid)+',',','+@projectid+',')>0
string query = "select A.projectname,A.projectid,A.Xi,B.CLx,B.UCLx,B.LCLx from(select projectname,value as Xi,projectid from TBLPAS_PRJ_TARGET where type = '进度控制' and charindex(','+ltrim(projectid)+',',','+@projectid+',')>0) as A,(select avg (CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) as CLx,avg(CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) + stdev(CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) as UCLx,avg(CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) - stdev(CAST(SUBSTRING(value,1,PATINDEX('%[^0.-9.]%',value)-1) as float)) as LCLx from TBLPAS_PRJ_TARGET where type = '进度控制' and charindex(','+ltrim(projectid)+',',','+@projectid+',')>0) as B ";SQL 语句改成这样.@projectid=循环赋值成 @projectid='101,102'即可....
create procedure protest
@s varchar(10)
as
declare @sql varchar(4000)
set @sql='select * from tb where col1 like ''%'+@s+'%'''
exec(@sql)
go
declare @s varchar(10)
set @s='a,b,c,d'
excute protest @s
你举个例子.
SqlConnection conn = new SqlConnection("User ID=sa;Initial Catalog=eprocms;Data Source=localhost");
conn.Open();
SqlCommand command = new SqlCommand();
command.CommandText = query;
command.Connection = conn;
SqlParameter sp1 = new SqlParameter("@projectid",SqlDbType.Char,200);
sp1.Direction = ParameterDirection.Input;
command.Parameters.Add(sp1);
sp1.Value = "1001" + "," + "1002";//这里的逗号不用引号怎么传呢?
set @projectid = '1001,1002'
select *
from 表
where charindex(','+rtrim(projectid)+',',','+@projectid+',')>0