过程
的结构如下:[dbo].[getSp] (
@Color varchar(10) = Null,
@Finish varchar(10) = Null,
@Height varchar(10) = Null,
@Type varchar(10) = Null,
@Trim varchar(10) = Null,
@Width varchar(10) = Null
)
这是搜索前数据的外观。此数据不完整,但作为获得想法的示例。每个组合都会在这里。
Color | Finish | Height | Type | Trim | Width |
Blue | Shiny | Tall | Toaster | Normal | 3.5 |
(NULL) | (NULL) | (NULL) | Toaster | (NULL) | 3.5 |
(NULL) | (NULL) | Tall | Toaster | Deco | 4 |
(NULL) | Shiny | Tall | Toaster | Deco | 4 |
Blue | Shiny | (NULL) | Toaster | Deco | 4 |
(NULL) | Shiny | Tall | Toaster | Deco | 4 |
(NULL) | Satin | Tall | Toaster | Deco | 4 |
Red | (NULL) | Tall | Toaster | Deco | 4 |
SELECT 语句将Type
作为主查找字段,因此它看起来像这样:
SELECT * FROM TABLE WHERE Type = @Type And
(
..... Confused about this part,
)
但基本上,我想运行一个存储过程来仅包含这些组合,这些组合是仅使用部分参数调用时在表中找到的行,如下所示:
[getSp]
@Color='Blue',
@Finish='Shiny',
@Type='toaster'
sp 最终会返回这个(每个组合)。
1. Blue Shiny Tall Toaster
2. Blue Shiny Toaster
3. Blue Tall Toaster
4. Blue Toaster
5. Tall Toaster
6. Shiny Toaster
7. Shiny Tall Toaster
所以我对如何在存储过程中构建 Where
语句以仅返回您包含在存储过程参数中的那些组合感到困惑,但其余参数(如 Width
和 Trim
将被排除在结果和搜索之外,默认情况下将作为NULL
传递。
我试过这个没有任何运气...
SELECT * FROM TABLE WHERE
Type = @Type AND
(
COLOR = COALESCE(@Color,COLOR) AND...
... for each other param
)
我试过这个没有任何运气...
SELECT * FROM TABLE WHERE
Type = @Type AND
(
(Color = @Color OR COALESCE(@Color,COLOR) IS NULL) OR...
... for each other param
)
我尝试构建sql语句,但也失败了。任何建议都会有所帮助。
这应该可以满足您的需求,如果您提供一个参数,那么它会使用它来过滤,否则它不会:
SELECT *
FROM table_1
WHERE (@Color IS NULL OR Color = @Color)
AND (@Finish IS NULL OR Finish = @Finish)
AND (@Height IS NULL OR Height = @Height)
AND (@Type IS NULL OR Type = @Type)
AND (@Trim IS NULL OR Trim = @Trim)
AND (@Width IS NULL OR Width = @Width)