PRB: Cannot Create NULL When You Call a Stored Procedure (820543)

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.

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

PRB: Cannot Create NULL When You Call a Stored Procedure (820543)

SYMPTOMS

RESOLUTION

STATUS

MORE INFORMATION