Microsoft KB Archive/820543

= PRB: Cannot Create NULL When You Call a Stored Procedure =

Article ID: 820543

Article Last Modified on 5/28/2003

-

APPLIES TO


 * Microsoft BizTalk Adapter for SQL

-



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

Keywords: kbprb KB820543

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.