April 5, 2007 at 1:59 pm
Hi, I have a table that has a default value specified for one of the columns.
The problem I am having with it is when an application calls a stored procedure that might pass 'null' as a value for that column, the default doesn not fill the column with the default value. I believe this is because SQL Server sees null as a value and not 'nothing' so this is why the default doesnt fire.
The application cannot change so I need to fins a way to handle puttin in a default if 'nothing' or 'null' is passed.
Thanks for you help.
e
April 5, 2007 at 2:10 pm
You are correct sir.
If the app is explicitly passing in a null then sql will insert the null. If you put a not null constraint on the field then app insert will fail.
Either have the application pass in the default, or your going to have to modify the stored procedures.
insert into Mytable(val1, val2, val3)
Select coalesce(@val1,0), coalesce(@val2,''), coalesce(@val3,'') etc.
April 5, 2007 at 2:28 pm
Could use a trigger too, although not sure I would recommend it...
create
table t (
C1 int
)
go
alter
trigger Te on t
instead
of insert
as
insert
into t
select
coalesce(C1,0) from inserted
April 5, 2007 at 3:01 pm
Thanks for the posts guys, I'll give this stuff a try.
thanks
e
April 6, 2007 at 3:42 am
You could also use the ISNULLL(@param1, 'enter default value here') to replace any nulls with the default value within your insert statement.
Catherine
Catherine Eibner
cybner.com.au
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply