sql server在存储过程的参数中为空或缺少值

8zzbczxx 于 2021-07-26 发布在 Java

关注(0)|答案(2)|浏览(540)

我有以下存储过程：

CREATE PROCEDURE oc.add_discount (@course_id INT = '',
                                @level VARCHAR(100) = '',
                                @language VARCHAR(100) = '',
                                @series_name VARCHAR(100) = '',
                                @discount DECIMAL(5,4) = 0.0)
AS
BEGIN
    SET @level = ISNULL(@level,'')
    SET @language = ISNULL(@language,'')
    SET @series_name = ISNULL(@series_name,'')
    UPDATE oc.price
    SET discount = @discount,
    --discount based always on the original price
        price = price/(1-discount)*(1-@discount)
    WHERE 
        (@course_id NOT IN (SELECT course_id FROM oc.courses)
            OR course_id IN (@course_id)) AND
        (@level NOT IN (SELECT level FROM oc.level)
            OR course_id IN (SELECT c.course_id 
                    FROM oc.courses c
                    JOIN oc.level l ON c.level_id = l.level_id 
                    WHERE l.level = LOWER(@level))) AND
        (@language NOT IN (SELECT language FROM oc.language)
            OR course_id IN (SELECT c.course_id
                    FROM oc.courses c
                    JOIN oc.language l ON c.language_id = l.language_id
                    WHERE l.language = UPPER(LEFT(@language,1))+LOWER(SUBSTRING(@language,2,LEN(@language))))) AND
        (@series_name NOT IN (SELECT series_name FROM oc.series)
            OR course_id IN (SELECT c.course_id
                    FROM oc.courses c
                    JOIN oc.series s ON c.series_id = s.series_id
                    WHERE s.series_name = @series_name))
    IF @@ROWCOUNT<1
        RAISERROR('Ooops something went wrong. No discount has been added. Avoid NULL or missing values, use '' instead!', 16, 0);
END;

我的问题是这个过程在大多数情况下都正常工作，但是如果一个参数为null，它不会更新任何内容，如果中间的一个参数丢失，我会得到一个错误。我用一条错误信息解决了这个问题，但我想找到一个更优雅的解决方案。有人能帮我吗？

EXEC oc.add_discount 0.1, 1                       ---- this query gives 10% discount for course ID 1
EXEC oc.add_discount 0.1, '', 'beginner', '', ''  ---- this query 10% discount for every beginner course
EXEC oc.add_discount 0.1, NULL, 'beginner', '','' ---- "0 rows affected"
EXEC od.add_discount 0.1, ,'beginner', ,          ---- "Incorrect syntax near ','."

sql sql-server tsql

来源：https://stackoverflow.com/questions/62468276/sql-server-null-or-missing-values-in-the-stored-procedures-parameters

2条答案

按热度按时间

vh0rcniy1#

通常最好按名称调用参数：这样，您可以按任何顺序调用参数。

EXEC oc.add_discount @course_id = 1, @level=''...

如果要使用有序参数调用过程：
您必须按顺序传递参数，并且可以跳过后续参数（前提是它们具有默认值）。
不能跳过中间的参数。
说到你的问题，如果你想跳过中间的参数，用默认值调用它们，而不是硬编码它们。

EXEC oc.add_discount 0.1, DEFAULT, 'beginner', DEFAULT, DEFAULT

或者，如果要传递空值，请传递空值并在过程中处理它们

EXEC oc.add_discount 0.1, NULL, 'beginner', NULL, NULL

在你的情况下，你要处理两次。我建议你按下面的方法做。

CREATE PROCEDURE oc.add_discount (@course_id INT = NULL,
                                @level VARCHAR(100) = NULL,
                                @language VARCHAR(100) = NULL,
                                @series_name VARCHAR(100) = NULL,
                                @discount DECIMAL(5,4) = NULL)
as

    SET @level = ISNULL(@level,'')
    SET @language = ISNULL(@language,'')
    SET @series_name = ISNULL(@series_name,'')
    SET @discount = ISNULL(@discount ,0.0)

如果不传递值，则会用null默认值填充该值并进行相应设置。

EXEC oc.add_discount 0.1

如果要在中间传递默认值，请使用 DEFAULT 参数

EXEC oc.add_discount 0.1,DEFAULT,1,DEFAULT,DEFAULT,1

赞(0）回复(0）举报 2021-07-26

3df52oht2#

据我所知，对于某些场景，您希望跳过一些参数。我建议您在执行存储过程时使用以下方法跳过参数。

EXEC od.add_discount @level='',@language='beginner'

但是，根据您的示例，我不建议对int参数使用引号。您应该考虑改用null。

赞(0）回复(0）举报 2021-07-26

我来回答

sql server在存储过程的参数中为空或缺少值

2条答案

相关问题

热门标签

最新问答