sql - SQL查询中的可选参数

Question

我是 SQL 的新手，我有点迷茫。我有一个表，其中包含产品、各种字段，例如 productname、category 等。

我想要一个查询，我可以这样说:选择某个类别中产品名称中包含特定单词的所有产品。复杂的因素是我只想返回该子集的特定范围。因此，我还想说将符合该规范的 100 到 120 种产品退还给我。

我用谷歌搜索并找到了这个查询:

WITH OrderedRecords AS
(   
    SELECT *, ROW_NUMBER() OVER (ORDER BY PRODUCTNUMMER) AS "RowNumber",
    FROM (
        SELECT * 
        FROM SHOP.dbo.PRODUCT
        WHERE CATEGORY = 'ARDUINO'
        and PRODUCTNAME LIKE '%yellow%'
    )
) 
SELECT * FROM OrderedRecords WHERE RowNumber BETWEEN 100 and 120
Go

查询在一定程度上有效，但是它在过滤之前分配了行号，所以我不会获得足够的记录，而且如果没有参数，我不知道如何处理它。理想情况下，我希望能够不提供类别和搜索词，它只会列出所有产品。

虽然我不知道如何实现这一目标，但不胜感激!

Answer 1

基于 esiprogrammer 在他关于如何使用分页仅返回特定范围内的行的回答中展示的内容。

你的第二个问题是:

Ideally I want to be able to not give a category and search word and it will just list all products.

您可以有两个查询/存储过程，一个用于使用特定参数进行查找，另一个用于不带参数的查找。

或者，如果您坚持为所有情况保留一个查询/存储过程，则有两种选择:

1. 构建一个只包含现有过滤器的动态 SQL 语句；使用 EXECUTE (@sql) 或 EXECUTE sp_executesql @sql
执行它
2. 构建一个包罗万象的查询

---

选项 2 的示例:

-- if no category is given, it will be NULL
DECLARE @search_category VARCHAR(128);

-- if no name is given, it will be NULL
DECLARE @search_name VARCHAR(128);

SELECT * 
FROM SHOP.dbo.PRODUCT
WHERE (@search_category IS NULL OR CATEGORY=@search_category) AND
      (@search_name IS NULL OR PRODUCTNAAM LIKE '%'+@search_name+'%')
ORDER BY PRODUCTNUMMER
OFFSET 100 ROWS
FETCH NEXT 20 ROWS ONLY
OPTION(RECOMPILE); -- generate a new plan on each execution that is optimized for that execution’s set of parameters