当SPROC变量为NULL时返回所有值

Question

是否有任何方法从下面返回“所有”类别，只需将SPROC输入变量保留为完全空白？

澄清-甚至不用“”

CREATE TABLE Levels (ID INT PRIMARY KEY
                    ,Value INT 
                    ,Category VARCHAR(10))

INSERT INTO Levels VALUES(1, 30, 'High') 
                        ,(2, 20, 'Medium') 
                        ,(3, 10, 'Low') 
GO

CREATE PROC LevelValues @Category VARCHAR(10)
AS
BEGIN
SELECT * 
FROM Levels
WHERE Category = CASE @Category WHEN '' THEN Category ELSE @Category END
END
GO

EXEC LevelValues ''

Answer 1

我认为您希望在不向参数传递任何值的情况下使用SP：

CREATE PROC LevelValues @Category VARCHAR(10) = ''
AS
BEGIN
SELECT * 
FROM Levels
WHERE Category = CASE @Category WHEN '' THEN Category ELSE @Category END
END
GO

EXEC LevelValues 

Answer 2

CREATE PROC LevelValues @Category VARCHAR(10)
AS
BEGIN
SELECT * 
FROM Levels
WHERE Category = @Category OR @Category = ''
END

在您的评论之后，这里有另一个解决方案：

CREATE PROC LevelValues @Category VARCHAR(10)
AS
BEGIN

IF @Category = ''
    SELECT * 
    FROM Levels
ELSE
    SELECT * 
    FROM Levels
    WHERE Category = @Category    
END

Answer 3

我推荐这样的东西

SELECT * FROM Levels WHERE Category = @Category OR @Category = ''

这将在输入为空时返回所有记录。

否则

SELECT * FROM Levels WHERE Category = @Category OR @Category = NULL