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

SYMPTOMS

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.

RESOLUTION

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.

STATUS

This behavior is by design.

MORE INFORMATION

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

Properties

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

Contact us for more help

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