PRB: Cannot Create NULL When You Call a Stored Procedure

Article translations Article translations
Article ID: 820543 - View products that this article applies to.
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all


When you use BizTalk Adapter for SQL Server, you might want to store a null value in the database that you are writing data to.

When you do an insert or an update, you can pass a value of IsNull as the value for a column. The IsNull value instructs BizTalk Adapter for SQL Server to store a null value in the column for that row. However, if you are running a stored procedure to pass the data, you cannot use a value of IsNull. Instead, you must change your stored procedure to assume a default value of null.


To insert null values in a column, change the stored procedure that you are running so that it assumes a null value for each column that allows null values.

When you call this stored procedure from BizTalk Adapter for SQL Server, and you do not pass a value for a column, the stored procedure inserts a null value in that column in the database.


This behavior is by design.


The following is a sample stored procedure that updates products in the Northwind Products database:
            @ProductName nvarchar(40), 
            @SupplierID int=null, 
            @CategoryID int=null, 
            @QuantityPerUnit nvarchar(20)=null, 
            @UnitPrice decimal(19,4)=null, 
            @UnitsInStock smallint=null, 
            @UnitsOnOrder smallint=null, 
            @ReorderLevel smallint=null, 
            @Discontinued bit 
UPDATE Products 
SET ProductName = @ProductName, 
            SupplierID = @SupplierID, 
            CategoryID = @CategoryID, 
            QuantityPerUnit = @QuantityPerUnit, 
            UnitPrice = @UnitPrice, 
            UnitsInStock = @UnitsInStock, 
            UnitsOnOrder = @UnitsOnOrder, 
            ReorderLevel = @ReorderLevel, 
            Discontinued = @Discontinued 
WHERE ProductName = @ProductName 



Article ID: 820543 - Last Review: February 27, 2014 - Revision: 1.2
  • Microsoft BizTalk Adapter for SQL
kbnosurvey kbarchive kbprb KB820543

Give Feedback


Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from