PRB: Cannot Create NULL When You Call a Stored Procedure

This article has been archived. It is offered "as is" and will no longer be updated.
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:
CREATE PROCEDURE UpdateProducts             @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 AS UPDATE Products SET ProductName = @ProductName,             SupplierID = @SupplierID,             CategoryID = @CategoryID,             QuantityPerUnit = @QuantityPerUnit,             UnitPrice = @UnitPrice,             UnitsInStock = @UnitsInStock,             UnitsOnOrder = @UnitsOnOrder,             ReorderLevel = @ReorderLevel,             Discontinued = @Discontinued WHERE ProductName = @ProductName GO

Article ID: 820543 - Last Review: 02/27/2014 21:18:59 - Revision: 1.2

  • Microsoft BizTalk Adapter for SQL
  • kbnosurvey kbarchive kbprb KB820543