DirectedInsight
  Home | Javascript | ASP | Database | Solutions | Misc.
NEWEST
Different ways to show the user they're wrong.
Helpful
Some simple queries that can save you some research time
NEWER
Complete AJAX example.
COOL
Dynamic file upload fields

small DI oval Passing NULL values to columns or parameters in a stored procedure

I ran into a situation where I getting unexpected behavior from a table and stored procedure where I had some default values set up. I had a table with a column that I did not want to accept NULL values so I set it up with a defualt of '' (an empty string). However, I was still getting the
"Cannot insert the value NULL into column ... column does not allow nulls" error.

After quite a bit of searching and testing I found the answer in a couple of places in the SQL Server Books On Line.

The first was in the "Allowing Null Values" section, the important sentence there is:
"A column defined with the keyword NULL also accepts an explicit entry of NULL from the user, no matter what data type it is or if it has a default associated with it."
That combined with the table on the "DEFAULT Definitions" page told me that even if the column is defined as "NOT NULL" and you supply a default value, if you pass NULL as a value for that column in your insert statement it will try to insert NULL and give an error. To get the default value you have to not supply a value for that column at all.

So what was wrong with my stored procedure, it should have used the default value instead of the NULL it was passed. Well, it turns out it didn't, and for pretty much the same reason. In the section on "Specifying a Default Value" it states that
"When the stored procedure is executed, the default value is used if no other value has been specified."
Which is the same problem I was having with the table itself, I was specifically passing NULL so it was using it.

small DI oval Solutions

There are two ways to correct this and eliminate the error. The first is to code two different calls to the stored procedure one with the parmeter when it is not NULL and one without it when the value is NULL, in the second case you would get the default value defined in the stored procedure. Alternately, you could place logic in the procedure itself to detect the NULL value and have two insert statements one with the column and a non NULL value and one without, the second call would get you the default value defined for the column. Here is the Transact-SQL for something like that: 

CREATE PROCEDURE dbo.saveOrderDetail

	@OrderID	VarChar(20),
	@ItemID		VarChar(30),
	@Qty		Int,
	@shipToName	VarChar(50)

AS

SET NOCOUNT ON

IF @shipToName IS NOT NULL 
	BEGIN
		INSERT INTO orderDetail (OrderID, ShipItem, QuantityOrdered, receiverName)
		Values(@OrderID, @ItemID, @Qty, @shipToName)
	END
ELSE
	BEGIN
		INSERT INTO orderDetail (OrderID, ShipItem, QuantityOrdered)
		Values(@OrderID, @ItemID, @Qty)
	END

SET NOCOUNT OFF
GO

In this example if the value for the @shipToName parameter is NULL is will use the second insert statement so that the default value set in the table is used.

For my situation there was a better solution. The data being inserted was coming from another table that held all of the data during the users session. All I had to do was make sure I wasn't getting a NULL value in the recordset returned from that table. What I used was the ISNULL function in that stored procedure. ISNULL is a handy function that will replace a NULL value with a default value you specify. The BOL syntax is ISNULL ( check_expression , replacement_value ). In my stored procedure it looks like this: 

SELECT OrderID, shipItem, quantityOrdered, ISNULL(shipToName, '') AS shipToName FROM .....

Now my recordset will contain the four fields from the table and the last field (shipToName) will either contain a valid, non NULL, name, or and empty string. I use that value to supply the value to the stored procedure parameter and I no longer need the check in the code for NULL or in the procedure itself.

With any luck the above will save someone else a bit of frustration, sure it is fairly basic and now that I've read the BOL and actually thought about what it said in those sections it makes perfect sense. Call me slow...

If you have a comment, better way to do it, or correction let us know comments@directedinsight.com

small DI oval