Optional parameters in SQL query

Question

I am new to SQL and I am kind of lost. I have a table that contains products, various fields like productname, category etc.

I want to have a query where I can say something like: select all products in some category that have a specific word in their productname. The complicating factor is that I only want to return a specific range of that subset. So I also want to say return me the 100 to 120 products that fall in that specification.

I googled and found this query:

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

The query works to an extent, however it assigns the row number before filtering so I won't get enough records and I don't know how I can handle it if there are no parameters. Ideally I want to be able to not give a category and search word and it will just list all products.

I have no idea how to achieve this though and any help is appreciated!

Optional parameters in SQL query

3 Answers