必须声明标量变量"@Brand"

Question

查询在SQL Server中运行良好，但在转移到Visual Studio查询生成器时，我得到错误

必须声明标量变量

查询的目的是显示最初筛选到某个类别的产品表中的结果，然后随着尺寸、颜色和品牌的选择，结果会被过滤掉。

该功能显示4个参数，类别、品牌、尺寸和颜色

SELECT        
ProdName, SellingPrice, BrandID, Size, Colour
FROM
ProductTbl
WHERE
(Category LIKE @category) 
AND (BrandID = CASE WHEN @Brand IS NULL THEN BrandID ELSE @Brand END) 
AND (Size = CASE WHEN @Size IS NULL THEN Size ELSE @Size END) 
AND (Colour = CASE WHEN @Colour IS NULL THEN Colour ELSE @Colour END)

为什么我会出现这个错误，我该如何修复它？

Answer 1

从查询中创建一个存储过程并调用它，这样就不需要声明这些变量。示例调用sproc_get_products 'SomeBrand','SomeColour','SomeCategory','SomeSize'

用于创建存储过程的代码

create proc sproc_get_prodcuts (
@Brand nvarchar(max), 
@Colour nvarchar(max), 
@Category nvarchar(max), 
@Size nvarchar(max) 
)
as
begin
SELECT        
ProdName, SellingPrice, BrandID, Size, Colour
FROM
ProductTbl
WHERE
(Category LIKE @category) 
AND (BrandID = CASE WHEN @Brand IS NULL THEN BrandID ELSE @Brand END) 
AND (Size = CASE WHEN @Size IS NULL THEN Size ELSE @Size END) 
AND (Colour = CASE WHEN @Colour IS NULL THEN Colour ELSE @Colour END)
end