Friday, November 03, 2006
On the project that I am leading, I ran across an interesting issue using a combination of a stored procedure and the ISNULL SQL function. If we have the following table definition:
CREATE TABLE [dbo].[TEST_TABLE] (
[Data] [varchar] (5) COLLATE Latin1_General_BIN NOT NULL
)
and this table contains a row that contains:
Data
=====
ABCDE
Now, if we have a stored procedure that updates the row called UpdateData:
CREATE PROCEDURE UpdateData
@Data varchar(3) = null
AS
Begin Tran
update TEST_TABLE
set Data = ISNULL(@Data, Data)
IF @@ERROR <> 0
Rollback Tran
else
Commit Tran
The stored procedure sets the @Data variable to null by defualt, otherwise it will use the value passed to it via the @Data variable. The update statement uses the ISNULL function to check if the @Data variable is the default value of null and if it is, then it should use the current value in the Data column on the table. If we call this stored procedure, without passing in the @Data parameter, the stored procedure will set the value of the @Data parameter to null, and this is what we'll get:
Data
=====
ABC
What happened, why did my data get truncated? Think about how the ISNULL function works, it sets the variable specified in the first parameter to the value of the variable specified in the second parameter if the first parameter is null. So, it should set the @Data variable to the value in the Data column. Also note that the stored procedure is implicitly creating the @Data variable in the procedure declaration and also note that the @Data variable has a width of 3 varchars instead of the 5 varchars in the TEMP_TABLE Data column declaration. Essentially what happens is that the ISNULL function sees that the @Data variable is null, reads the Data column from the table and then copies the value from the Data column into the @Data variable. Since the @Data variable is only 3 varchars wide, only 3 varchars are copied to it. The set clause then updates the Data column with these 3 varchars from the @Data variable. That's why the data gets truncated. So, the moral of the story is to make sure of the following:
CREATE TABLE [dbo].[TEST_TABLE] (
[Data] [varchar] (5) COLLATE Latin1_General_BIN NOT NULL
)
and this table contains a row that contains:
Data
=====
ABCDE
Now, if we have a stored procedure that updates the row called UpdateData:
CREATE PROCEDURE UpdateData
@Data varchar(3) = null
AS
Begin Tran
update TEST_TABLE
set Data = ISNULL(@Data, Data)
IF @@ERROR <> 0
Rollback Tran
else
Commit Tran
The stored procedure sets the @Data variable to null by defualt, otherwise it will use the value passed to it via the @Data variable. The update statement uses the ISNULL function to check if the @Data variable is the default value of null and if it is, then it should use the current value in the Data column on the table. If we call this stored procedure, without passing in the @Data parameter, the stored procedure will set the value of the @Data parameter to null, and this is what we'll get:
Data
=====
ABC
What happened, why did my data get truncated? Think about how the ISNULL function works, it sets the variable specified in the first parameter to the value of the variable specified in the second parameter if the first parameter is null. So, it should set the @Data variable to the value in the Data column. Also note that the stored procedure is implicitly creating the @Data variable in the procedure declaration and also note that the @Data variable has a width of 3 varchars instead of the 5 varchars in the TEMP_TABLE Data column declaration. Essentially what happens is that the ISNULL function sees that the @Data variable is null, reads the Data column from the table and then copies the value from the Data column into the @Data variable. Since the @Data variable is only 3 varchars wide, only 3 varchars are copied to it. The set clause then updates the Data column with these 3 varchars from the @Data variable. That's why the data gets truncated. So, the moral of the story is to make sure of the following:
- Your table column widths and your stored procedure widths match
- You pass the right amount of data from your code to your stored procedure
