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

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.

Problem

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 .“