>>>>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.