最近弄1个查询报表,遇到个问题,例如 有俩个类别 笔记本电脑 和 手机 ,但要求还要 以 笔记本电脑+手机的形式进行查询,以下是例子
CREATE TABLE PRODUCTS
(
PID INT IDENTITY(1,1) PRIMARY KEY,
ProductCategory varchar(20)
)INSERT INTO PRODUCTS
SELECT '笔记本电脑'
UNION ALL
SELECT '手机'go
CREATE PROC TestProducts
(
@ProductCategory varchar(20)
)
AS
BEGIN
select * from PRODUCTS where ProductCategory in (''+@ProductCategory+'')
END
GO
DECLARE @ProductCategory varchar(20)
--SELECT @ProductCategory = '笔记本电脑','手机'
EXEC TestProducts @ProductCategory从程序汇总获取的参数 是 string a = “'笔记本电脑','手机'”;这样的,但传递给
EXEC TestProducts @ProductCategory 有错误,请教大家 有没有好的方案!
CREATE TABLE PRODUCTS
(
PID INT IDENTITY(1,1) PRIMARY KEY,
ProductCategory varchar(20)
)INSERT INTO PRODUCTS
SELECT '笔记本电脑'
UNION ALL
SELECT '手机'go
CREATE PROC TestProducts
(
@ProductCategory varchar(20)
)
AS
BEGIN
select * from PRODUCTS where ProductCategory in (''+@ProductCategory+'')
END
GO
DECLARE @ProductCategory varchar(20)
--SELECT @ProductCategory = '笔记本电脑','手机'
EXEC TestProducts @ProductCategory从程序汇总获取的参数 是 string a = “'笔记本电脑','手机'”;这样的,但传递给
EXEC TestProducts @ProductCategory 有错误,请教大家 有没有好的方案!
(
@ProductCategory varchar(20)
)
AS
BEGIN
select * from PRODUCTS where ProductCategory in (@ProductCategory)
END
GO
CREATE PROC TestProducts
(
@ProductCategory varchar(500)
)
AS
BEGIN
select * from PRODUCTS where ','+@ProductCategory+',' LIKE '%,'+ProductCategory +',%'
END
GOEXEC TestProducts '笔记本电脑,手机'改為這樣
DECLARE @ProductCategory varchar(20)
set @ProductCategory = '''笔记本电脑'',''手机'''
EXEC TestProducts @ProductCategory
(
@ProductCategory varchar(20)
)
AS
BEGIN
declare @str nvarchar(max)
set @str='select * from PRODUCTS where ProductCategory in ('+@ProductCategory+')'
exec(@str)
END
GO
DECLARE @ProductCategory nvarchar(20)
SET @ProductCategory = '''笔记本电脑'',''手机'''
EXEC TestProducts @ProductCategory
select * from PRODUCTS where ProductCategory in (''+@ProductCategory+'')
...
-->declare @sql varchar(4000)
set @sql='select * from PRODUCTS where ProductCategory in ('+@ProductCategory+')'
exec (@sql)
select * from PRODUCTS where ProductCategory in ('+@ProductCategory+')-- 单引号
END
GO