sql - 在存储过程中按参数过滤

Question

我的代码有问题:P 显然是对的？!无论如何......这就是它是什么......我想要一个存储过程，它需要3个参数(@cat，@cutPrice，@option)并返回多个具有类似于@cat的类型的结果，并且价格是< 或 > @cutPrice 取决于@option 关键字“above”或“below”。问题是当我执行这个...

EXEC spBookByPrice  
@cat = 'business', @cutPrice = 0.00, @option = 'above'  

...我没有得到任何结果，但@option = ' ' 显示了所有价格。无论如何，这是我的代码....

ALTER PROC spBookByPrice
@cat varchar(12), @cutPrice money, @option varchar(5)
AS
BEGIN
    SELECT
    title AS 'Title:',
    type AS 'Category:',
    price AS 'Price:',
    CASE 
        WHEN price >= @cutPrice THEN 'above'
        WHEN price < @cutPrice THEN 'below'
        --ELSE NULL
    END AS 'Option:'
    FROM dbo.titles
    WHERE 'Option:' LIKE '%' + @option + '%'
    GROUP BY type, title, price
    HAVING type LIKE '%' + @cat + '%'
END

Answer 1

这是你需要的:

ALTER PROC spBookByPrice
@cat varchar(12), @cutPrice money, @option varchar(5)
AS
BEGIN
    SELECT
    title AS [Title:],
    type AS [Category:],
    price AS [Price:],
    CASE 
        WHEN price >= @cutPrice THEN 'above'
        WHEN price < @cutPrice THEN 'below'
        --ELSE NULL
    END AS [Option:]
    FROM dbo.titles
    WHERE (CASE WHEN price >= @cutPrice THEN 'above'
                WHEN price < @cutPrice THEN 'below'
            --ELSE NULL
            END) LIKE '%' + @option + '%'
    GROUP BY type, title, price
    HAVING type LIKE '%' + @cat + '%'
END