8

I want to create a stored procedure. One of the parameters ....

@ID int not null,

is giving me an error:

NOT NULL parameters are only supported with natively compiled modules, except for inline table-valued functions.

how can I fix this?

Martin
  • 16,093
  • 1
  • 29
  • 48
Cheri Choc
  • 131
  • 2
  • 11
  • What are you actually trying to achieve with your SP? Have a look at: https://stackoverflow.com/questions/47532445/accepting-nulls-into-stored-procedures – Martin Jan 03 '19 at 09:23
  • Possible duplicate of [How to restrict NULL as parameter to stored procedure SQL Server?](https://stackoverflow.com/questions/330303/how-to-restrict-null-as-parameter-to-stored-procedure-sql-server) – EzLo Jan 03 '19 at 09:27

2 Answers2

6

if you want to have required parameter for a stored procedure then just declare the parameter like below, then check whether param is null or not

Create procedure MyProc
@ID int
as
    if @ID is null
    begin
      -- handle the null case here
    end
--  rest of your query

If the parameter is optional then assign null to the parameter, which acts as a default value if no input is provided in procedure call

Create procedure MyProc
@ID int = null
as
--  your query
Vinit
  • 2,540
  • 1
  • 15
  • 22
  • 2
    This will not stop you to pass `NULL` in the parameter, i.e. to call it like this `exec MyProc NULL`. So this isn't an answer to OP's question. – Andrey Nikolov Jan 03 '19 at 09:31
1

You can throw an error if the parameter is null in the beginning of your SP 

IF @ID IS NULL 
  BEGIN 
      RAISERROR (15600,-1,-1,'myProcedure'); 
  END
Anson Aricatt
  • 391
  • 2
  • 8