存储过程里有一个and (c.Name = @OtherUnit)，如何给@OtherUnit加入多个条件？

[Spring414]

本来@OtherUnit只有一个单位，现在@OtherUnit会有多个单位，就是让@OtherUnit成为这个样子：'只' or c.Name = '袋'，但总是出错。

像这样没有结果：
declare @a varchar(100)
set @a = '''9'' or 1=1'
select 9 where ('9' = @a)

谢谢了。

[Spring414]

up

[yujohny]

declare @a varchar(100)
set @a = '''9'' or 1=1'
exec('select 9 where 9= '+ @a)

[yujohny]

我想你应该改成这样
declare @OtherUnit varchar(100)
set @OtherUnit='[''只'',''袋'']'

select('select * from 表 where [Name] in'+@OtherUnit)

[yujohny]

用exec来执行动态语句，你根据我的代码来具体修改
declare @OtherUnit varchar(100)
set @OtherUnit='[''只'',''袋'']'

exec('select * from 表 where [Name] in'+@OtherUnit)

[Spring414]

可不可以不改存储过程，直接给@OtherUnit赋值？

[yujohny]

可是可以，那样变量就失去了意义了，你就要写c.name进去变量
declare @OtherUnit varchar(100)
set @OtherUnit='''只'' or c.[Name]=''袋'''

exec('select * from 表 where c.[Name] in'+@OtherUnit)

[zjcxc]

可不可以不改存储过程，直接给@OtherUnit赋值？

你原来的存储过程是怎样写的?

[Spring414]

yujohny我的存储过程里不用exec的啊。
是这样的：

CREATE PROCEDURE ShowStorageGoods
@StorageLvl varchar(1000), @GoodsLvl varchar(1000), @MinNumber decimal(18,4),@OtherUnit varchar(1000)

AS

select
d.Name as Storage,
b.ID as GoodsID,
b.InputCode,
b.Name as GoodsName,
c.Name as Unit,
SUM(a.Number/c.UnitRate) as Number
from
Storage_Goods a,
Base_Goods b,
Base_Unit c,
(SELECT ID,Name
FROM Base_Storage
WHERE Lvl LIKE @StorageLvl AND Tag = '0') d,
(SELECT ID,Name
FROM Base_GoodsType
WHERE Lvl LIKE @GoodsLvl AND Tag = '0') e
where
a.GoodsID = b.ID
and a.StorageID = d.ID
and a.Number > @MinNumber
and c.MainID = b.ID
and b.GoodsTypeID = e.ID
and (c.Name = @OtherUnit)
group by d.Name,b.Id,b.InputCode,b.Name,c.Name
Order by d.Name,b.InputCode

GO

[zjcxc]

--可以这样改.
CREATE PROCEDURE ShowStorageGoods
@StorageLvl varchar(1000), @GoodsLvl varchar(1000), @MinNumber decimal(18,4),@OtherUnit varchar(1000)

AS

select
d.Name as Storage,
b.ID as GoodsID,
b.InputCode,
b.Name as GoodsName,
c.Name as Unit,
SUM(a.Number/c.UnitRate) as Number
from
Storage_Goods a,
Base_Goods b,
Base_Unit c,
(SELECT ID,Name
FROM Base_Storage
WHERE Lvl LIKE @StorageLvl AND Tag = '0') d,
(SELECT ID,Name
FROM Base_GoodsType
WHERE Lvl LIKE @GoodsLvl AND Tag = '0') e
where
a.GoodsID = b.ID
and a.StorageID = d.ID
and a.Number > @MinNumber
and c.MainID = b.ID
and b.GoodsTypeID = e.ID
and charindex(c.Name, @OtherUnit)>0
group by d.Name,b.Id,b.InputCode,b.Name,c.Name
Order by d.Name,b.InputCode

GO

[Spring414]

好像可以了。

[qdubit]

declare @OtherUnit varchar(100)
set @OtherUnit='[''只'',''袋'']'

select('select * from 表 where [Name] in'+@OtherUnit)
这样就可以了。

[yujohny]

CREATE PROCEDURE ShowStorageGoods
@StorageLvl varchar(1000), @GoodsLvl varchar(1000), @MinNumber decimal(18,4),@OtherUnit varchar(1000)

AS

select
d.Name as Storage,
b.ID as GoodsID,
b.InputCode,
b.Name as GoodsName,
c.Name as Unit,
SUM(a.Number/c.UnitRate) as Number
from
Storage_Goods a,
Base_Goods b,
Base_Unit c,
(SELECT ID,Name
FROM Base_Storage
WHERE Lvl LIKE @StorageLvl AND Tag = '0') d,
(SELECT ID,Name
FROM Base_GoodsType
WHERE Lvl LIKE @GoodsLvl AND Tag = '0') e
where
a.GoodsID = b.ID
and a.StorageID = d.ID
and a.Number > @MinNumber
and c.MainID = b.ID
and b.GoodsTypeID = e.ID
and (c.Name = @OtherUnit)--这句改为 (c.Name in @OtherUnit)
group by d.Name,b.Id,b.InputCode,b.Name,c.Name
Order by d.Name,b.InputCode

GO

然后赋@OtherUnit为'[''只'',''袋'']'

[Spring414]

只能这样的：and charindex(c.Name, @OtherUnit)>0
and (c.Name = @OtherUnit)--这句改为 (c.Name in @OtherUnit)不行的。
结贴!
谢谢大家！