In this article, you will know about the error “Msg 11555 NOT NULL parameters are only supported with natively compiled modules, except for inline table-valued functions.” Also, we will discuss why this error arises and what things to do to resolve this error.
Recently, I was working with the SQL Server Stored Procedure Parameters. I was trying to implement the NOT NULL constraint with the stored procedure parameters.
Basically, I wanted to ensure that the stored procedure parameters do not contain a NULL value. Therefore, I was trying to apply the NOT NULL constraint to the stored procedure parameters. I wrote the SQL code as:
USE master GO CREATE PROCEDURE dbo.TestProcedure @ID int NOT NULL AS BEGIN SELECT * FROM dbo.Student WHERE [College ID]= @ID END
The error can also come as not null parameters are only supported with natively compiled stored procedures.
- Later, I realized that this is the incorrect way.
Msg 11555 NOT NULL parameters are only supported with natively compiled modules, except for inline table-valued functions Solution:
- I wanted to verify that stored procedure parameter does not contain a NULL value.
- The correct way of doing this is to verify this thing in the stored procedure body, not at the time of parameter declaration.
- Here is how:
USE master GO CREATE PROCEDURE dbo.TestProcedure @ID int AS BEGIN BEGIN TRY IF @ID IS NOT NULL SELECT * FROM dbo.Student WHERE [College ID]= @ID ELSE RAISERROR('NULL Value is passed',15,1) END TRY BEGIN CATCH PRINT('A Null Value is passed') END CATCH END
- It is a good approach to verify the NULL value in this way using exception handling.
- If the value passes is NULL, the stored procedure will throw an error.
You may like the following SQL server tutorials:
- SQL Server stored procedure return value
- How to select latest record in SQL Server
- SQL Server stored procedure insert into with examples
- Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements
This was my experience when I encountered the error “Msg 11555 NOT NULL parameters are only supported with natively compiled modules, except for inline table-valued functions .“
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.