How to declare a not null parameter into Store procedure in a right way?

Question

I wanna write a store procedure, which takes 4 parameters and one parameters must be entered, in order to find the right product to update it from the lager under different conditions. I do not know, where is my mistake. When i execute, i get such error The parameter '@productName' has been declared as NOT NULL. NOT NULL parameters are only supported with natively compiled modules, except for inline table-valued functions. I really appreciate your gently and kindly feedback to improve myself. I could not find the right answer online.

Here is my Code:

create procedure dbo.Update_Lager (@productName varchar (50) not null, @info varchar(30) null, @amount int null, @Preis float null)
as begin
    if (@productName != null)
        begin 
            if  (@info !=null and @amount !=null and @Preis != null)
        begin
            update Artikel set Info = @info, Anzahl = @amount, Preis = @Preis where Produktbezeichnung = @productName
        end
    else if (@info != null)
        begin
            update Artikel set Info = @info where Produktbezeichnung = @productName
        end
    else if (@amount!=null)
        begin
            update Artikel set Anzahl = @amount where Produktbezeichnung = @productName
        end
    else if (@Preis != null)
        begin
            update Artikel set Preis = @Preis where Produktbezeichnung = @productName
        end
    end
    else
        begin
            print ('You must enter the product name!')
        end
end

And this is the table.

Thom A · Answer 1 · 2020-06-18T14:13:00.797

Like the error tells you, SQL Server doesn't support NOT NULL parameters unless you're using natively compiled modules.

Also, in your query, @productName != null is never going to be True. Nothing is equal to, or not equal to NULL, including NULL itself. Both NULL = NULL and NULL != NULL returns "unknown", which although isn't false, it's more importantly not True (which is what we're testing for in boolean expressions). The only way to compare NULL values is with IS NULL and IS NOT NULL.

Back to the question, however, if you need to implement a NON NULL parameter in a Procedure that isn't natively compiled, then you need to handle it within the Procedure. If you want to cause an error, then use THROW and a custom error message and number:

USE Sandbox;
GO

CREATE PROC dbo.TestProc @NullParam int = NULL, @NonNullParam int AS
BEGIN

    IF @NonNullParam IS NULL
        THROW 75001, N'Procedure ''TestProc'' expects a non-NULL value for parameter ''@NonNullParam'', which was not supplied.',16; --Choose a error number appropriate for you

    SELECT @NullParam + @NonNullParam;
END;
GO
--Works
EXEC dbo.TestProc @NullParam = 1,
                  @NonNullParam = 1;
GO

--Works (returns NULL)
EXEC dbo.TestProc @NonNullParam = 1;
GO
--Fails, as @NonNullParam not supplied
EXEC dbo.TestProc @NullParam = 1;
GO

--Fails, as @NonNullParam has NULL value
EXEC dbo.TestProc @NullParam = 1,
                  @NonNullParam = NULL;
GO

DROP PROC dbo.TestProc;

DB<>Fiddle

Larn**u** @Patrick ... Please at least spell my alias correctly. ;) — Thom A, Jun 18 '20 at 14:18

Liaqat Kundi · Accepted Answer · 2020-06-18T14:26:57.423

1

you can try it like the below. Null and Not Null does not support in Parameter of Store Procedure


Create procedure dbo.Update_Lager (@productName varchar (50)='', 
@info varchar(30)=null, @amount int=null, @Preis float=null)
as begin
    if (@productName is not null)
        begin 
            if  (@info is not null and @amount is not null and @Preis is not null)
        begin
            update Artikel set Info = @info, Anzahl = @amount, Preis = @Preis where Produktbezeichnung = @productName
        end
    else if (@info is not null)
        begin
            update Artikel set Info = @info where Produktbezeichnung = @productName
        end
    else if (@amount is not null)
        begin
            update Artikel set Anzahl = @amount where Produktbezeichnung = @productName
        end
    else if (@Preis is not null)
        begin
            update Artikel set Preis = @Preis where Produktbezeichnung = @productName
        end
    end
    else
        begin
            print ('You must enter the product name!')
        end
end

edited Jun 18 '20 at 14:26

answered Jun 18 '20 at 14:09

Liaqat Kundi

71
6

1

As I explain in my own answer, this isn't going to work. This will **always** `PRINT` the message `'You must enter the product name!'` – Thom A Jun 18 '20 at 14:16
hey thanks for your reply! when i write such a statement **exec dbo.Update_Lager 'Beans', 1.46;**, it doesn't update anything! could you guide me with last step as well, please? – Patrick Jun 18 '20 at 14:17
1

Use **Is not null** instead of **!=Null** – Liaqat Kundi Jun 18 '20 at 14:21
thanks, now it worked but it updates the wrong fields, When i write **exec dbo.Update_Lager 'Beans', 1.46;**, it updates info field into Info columns and when i write **exec dbo.Update_Lager 'Beans','2kg bags' , 1.46;**, it still updates same field into same column but not other fields in other columns. – Patrick Jun 18 '20 at 14:39
1

according to your above mention parameter, it will execute this query **update Artikel set Info = @info where Produktbezeichnung = @productName** – Liaqat Kundi Jun 18 '20 at 14:46

score 1 · Answer 3 · answered Jun 18 '20 at 14:20

You can try the other way round. Check for product name is NULL.

CREATE PROCEDURE dbo.Update_Lager (@productName varchar (50), @info varchar(30) , @amount int , @Preis float )
AS
BEGIN
  IF (@productName IS NULL)
    RAISERROR('You must enter the product name!', 15, 1) -- with log  
  ELSE
    BEGIN 
    ---------------------------------
    ---do everything else here------     
    Select @productName as productname, @info as info, @amount as amount, @Preis as preis
    ----------------------------------
    END
END

Check 1:

EXEC dbo.Update_Lager NULL,'xxx',10,5.0

Result:

Msg 50000, Level 15, State 1, Procedure dbo.Update_Lager, Line 5 [Batch Start Line 58]
You must enter the product name!

Check 2:

EXEC dbo.Update_Lager 'abcd','xxx',10,5.0

Result

productname info    amount preis
abcd        xxx     10     5

Took inspiration from here : How to restrict NULL as parameter to stored procedure SQL Server?

FYI, it is recommended that `THROW` is used over `RAISERROR` now. From the [documentation](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-ver15): *"The **RAISERROR** statement does not honor **SET XACT_ABORT**. New applications should use **THROW** instead of **RAISERROR**.*" — Thom A, Jun 18 '20 at 14:22
hey, thanks a lot for your reply. working on your answer ..... — Patrick, Jun 18 '20 at 14:40

How to declare a not null parameter into Store procedure in a right way?

3 Answers3