SQL Server Null or missing values in the stored procedure's parameters

Question

I have the following stored procedure:

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;

My problem is that the procedure is working properly in most of the cases however if a parameter is null it doesn't update anything and if a parameter in the middle is missing I get an error. I managed the problem with an error message but I would like to find a more elegant solution. Could anyone help me,please?

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 ','."

Does this answer your question? [How can I use optional parameters in a T-SQL stored procedure?](https://stackoverflow.com/questions/3415582/how-can-i-use-optional-parameters-in-a-t-sql-stored-procedure) — underscore_d, Jun 19 '20 at 10:33
There are four examples underneath with the results after the queries. Or maybe I don't understand what you are asking? — Katalin Szeghalmi, Jun 19 '20 at 10:52
"works" is not an example. I meant sample input data and output data, or in this case an indication which rows would be affected, or at least a row count. — underscore_d, Jun 19 '20 at 10:53
I see,sorry. `EXEC oc.add_discount 0.1, 1 ` ----this one gives 10% discount for course ID 1 `EXEC oc.add_discount 0.1, '', 'beginner', '', '' ` ----this one gives 10% discount for every beginner course — Katalin Szeghalmi, Jun 19 '20 at 10:57
I know but what does it have to do with it? I could write 'unknown' instead of '' but the problem is the same. — Katalin Szeghalmi, Jun 19 '20 at 11:39
@KatalinSzeghalmi . . . You have neglected to describe what behavior you want when a parameter is `NULL`. — Gordon Linoff, Jun 19 '20 at 11:43
@GordonLinoff that's why I have this part: `SET @level = ISNULL(@level,'') SET @language = ISNULL(@language,'') SET @series_name = ISNULL(@series_name,'')` How else could I do? could you give me a hint? — Katalin Szeghalmi, Jun 19 '20 at 11:48

score 2 · Accepted Answer · answered Jun 19 '20 at 12:02

Generally it is preferred to call parameters by name: In this way, you can call the parameters in any order.
```
EXEC oc.add_discount @course_id = 1, @level=''...
```
If you want to call procedure using ordered parameters:
- you have to pass the parameters in order and you can skip the subsequent parameters(provided they have got default values).
- you cannot skip parameters in the middle.

Coming to your question, if you want to skip the middle parameters and call them with default values, instead of hardcoding them.

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

Or, if you want to pass null values, pass null values and handle them inside the procedure

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

In your case, you are handling it twice. I would suggest you to do in the below way.

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)

If you don't pass value, it is filled with NULL default value and set accordingly.

EXEC oc.add_discount 0.1

If you want to pass default value in the middle, call with DEFAULT parameter

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

Thanks,dealing with NULLs solved. :) However the last query has too many arguments and doesn't work. Did you mean `EXEC od.add_discount 0.1, DEFAULT,'beginner',DEFAULT , DEFAULT`? If I run this one it gives me this error: **Could not find stored procedure 'od.add_discount'.** Do you have any explanation for this? — Katalin Szeghalmi, Jun 19 '20 at 12:28
I just gave example. It seems your schema is oc. you have to call `oc.add_discount`. You need to pass right number of parameters — Venkataraman R, Jun 19 '20 at 12:40
Omg it was just a typo and I almost went crazy. :D Thanks a lot for your help. — Katalin Szeghalmi, Jun 19 '20 at 12:46

score 1 · Answer 2 · answered Jun 19 '20 at 10:38

1

As far as I understood, you want to skip some parameters for some scenarios. I recommend you to use the following approach to skip parameters when executing the Stored Procedure.

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

However, as per your example, I don't recommend to use quotations for INT parameter. You should consider using NULL instead.

answered Jun 19 '20 at 10:38

Spider

514
1
10
22

If I use NULL it doesn't do anything, that's why I used empty string instead. – Katalin Szeghalmi Jun 19 '20 at 11:00
Then I guess you haven't addressed the null properly. – Spider Jun 19 '20 at 11:01
What you mean? Could you explain me this? – Katalin Szeghalmi Jun 19 '20 at 11:28
@KatalinSzeghalmi - This is a separate question. Kindly start a new topic. – Spider Jun 19 '20 at 11:51

SQL Server Null or missing values in the stored procedure's parameters

2 Answers2