Nulls and defaults

[timscronin]

I have a sp which populates a table. the SP resides on the same server/db as the table. Several of the values are assigned nulls and then inserted into the table. I have 4 defaults on some of these fields. However, instead of loading the default it puts a blank. ANy ideas

 

[gmmastros]

If you want to use the default value for a column, then you shouldn't be inserting ANYTHING in to that column (even a value of null). Take a look at this code...

[COLOR=blue]Create[/color] [COLOR=blue]Table[/color] #TestDefault(Id [COLOR=blue]Int[/color], Data [COLOR=blue]VarChar[/color](100) [COLOR=blue]Default[/color] [COLOR=red]'This is blank'[/color])

[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] #TestDefault [COLOR=blue]Values[/color](1, NULL)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] #TestDefault(Id) [COLOR=blue]Values[/color](2)

[COLOR=blue]Select[/color] * [COLOR=blue]from[/color] #TestDefault

[COLOR=blue]Drop[/color] [COLOR=blue]Table[/color] #TestDefault

Notice that the first insert does insert a value (NULL) for the data column, whearas the second one does not insert a value.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Oh... Yeah... I didn't really help you solve your problem, did I. Sorry.

What you could do is get the default values for the column that you are inserting and insert that instead of null. You can determine what the default value is by using the information_schema.columns view.

[COLOR=blue]Select[/color] * 
[COLOR=blue]from[/color]   Information_Schema.Columns 
[COLOR=blue]Where[/color]  Table_Name = [COLOR=red]'[!]YourTableNameHere[/!]'[/color]

There will be a column returned named [!]Column_Default[/!].

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

or you could make the parameters of the sp not required and then only insert the ones you need.

Questions about posting. See faq183-874