我有一个存储过程,它非常简单,只有一个选择语句,但是 where 子句会根据参数而变化。
我正在使用 if else 语句并写出选择 4 次,有没有更好的方法来简化这个存储过程?
IF @Status > 0
BEGIN
IF @Group = ''
BEGIN
SELECT *
FROM ( SELECT tbl.* ,
ROW_NUMBER() OVER ( ORDER BY tbl.NDB_No ) rownum
FROM dbo.FoodAbbrev AS tbl
WHERE Status = @Status
) seq
WHERE seq.rownum BETWEEN @X AND @Y
ORDER BY seq.rownum
END
ELSE
BEGIN
SELECT *
FROM ( SELECT tbl.* ,
ROW_NUMBER() OVER ( ORDER BY tbl.NDB_No ) rownum
FROM dbo.FoodAbbrev AS tbl
WHERE Status = @Status
AND GroupCd = @Group
) seq
WHERE seq.rownum BETWEEN @X AND @Y
ORDER BY seq.rownum
END
END
ELSE
BEGIN
IF @Group = ''
BEGIN
SELECT *
FROM ( SELECT tbl.* ,
ROW_NUMBER() OVER ( ORDER BY tbl.NDB_No ) rownum
FROM dbo.FoodAbbrev AS tbl
) seq
WHERE seq.rownum BETWEEN @X AND @Y
ORDER BY seq.rownum
END
ELSE
BEGIN
SELECT *
FROM ( SELECT tbl.* ,
ROW_NUMBER() OVER ( ORDER BY tbl.NDB_No ) rownum
FROM dbo.FoodAbbrev AS tbl
WHERE GroupCd = @Group
) seq
WHERE seq.rownum BETWEEN @X AND @Y
ORDER BY seq.rownum
END
END
END
将所有这些IF
语句替换为以下带有可选参数SELECT
语句:
SELECT *
FROM ( SELECT tbl.* ,
ROW_NUMBER() OVER ( ORDER BY tbl.NDB_No ) rownum
FROM dbo.FoodAbbrev AS tbl
WHERE 1 = 1
AND (@Group IS NULL OR GroupCd = @Group)
AND (@Status IS NULL OR Status = @Status)
) seq
WHERE seq.rownum BETWEEN @X AND @Y
ORDER BY seq.rownum
SELECT *
FROM ( SELECT tbl.* ,
ROW_NUMBER() OVER ( ORDER BY tbl.NDB_No ) rownum
FROM dbo.FoodAbbrev AS tbl
WHERE ISNULL(Status,0) = CASE WHEN @Status > 0 THEN @Status ELSE ISNULL(Status,0) END
AND ISNULL(GroupCd, '') = CASE WHEN @Group = '' THEN ISNULL(GroupCd, '') ELSE @Group END
) seq
WHERE seq.rownum BETWEEN @X AND @Y
ORDER BY seq.rownum