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