>>>>>It's actually an interesting question - we want to use default value if that @Value is NULL. I'll ran a test using DEFAULT keyword - thinking it may be tricky.
>>>>
>>>>I tried it and it gave me an error.
>>>
>>>You need to use dynamic SQL for this purpose. You can not use DEFAULT directly.
>>>
>>>
>>>create table #test (dateFld datetime default '19000101', charField varchar(20) default 'Test', numField int default 10)
>>>
>>>declare @dt datetime = null, @ch varchar(20) = null, @nm int =15
>>>
>>>declare @sql nvarchar(max)
>>>
>>>set @sql = N'INSERT INTO #Test (dateFld, charField, numField)
>>>VALUES (' + case when @dt IS NULL then 'DEFAULT' else '@dt' END +
>>>', ' + case when @ch IS NULL then 'DEFAULT' else '@ch' end +
>>>', ' + case when @nm is NULL then 'DEFAULT' else '@nm' end + ')'
>>>
>>>execute sp_executeSQL @sql, N'@dt datetime, @ch varchar(20), @nm int', @dt = @dt,@ch=@ch, @nm=@nm
>>>
>>>select * from #test
>>
>>Can't you then modify your dynamic Insert to skip the fields if their are null? And skip the value if null in the Values part?
>
>Yes, it can be written this way as well but then you would need to use the same expressions twice (e.g. you would test for null value two times). I used the simpler road of using DEFAULT values, it is the same as skipping the column in the insert.
I understand your logic.
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham