user default column value in INSERT stored procedure

Question

From my ASP.NET application I am calling an INSERT stored procedure, there is a default value for one of the columns in the table that the INSERT procedure places the data into. How do I specify that I want the default value to be used instead? (Or do I have to specify the actual default value in my parameters)

SITUATIONAL TABLE:

Column 1: int
Column 2: string
Column 3: int default -1

SITUATIONAL STORED PROCEDURE

INSERT INTO TABLE
  (Column 1, Column 2, Column 3)
VALUES
  (?, ?, ?)

SITUATIONAL ASP.NET CODE

create paramter (Column 1, 12)
if (x = 0)
 {create parameter (Column 2, "test")}
else
 {
   create parameter (Column 3, 24)
   create parameter (Column 2, DBNull.Value)
 }

Thank you for your help

Answer 1

Leave the column out of the insert clause, and you will get the default.

For example in your procedure:

IF condition
  INSERT INTO Y (X) VALUES(1); -- will use defaults
ELSE
  INSERT INTO Y (W, X) VALUES(1, 2) -- won't use default for W

Answer 2

You can use the default keyword.

CREATE TABLE #T
(
i int default 12,
j int
)

INSERT INTO #T (i,j)  VALUES (DEFAULT, 10)

SELECT i,j FROM #T
DROP TABLE #T

Answer 3

What about

if (somecondition) { // User another value for c2
   query = "insert into table_name (c1, c2) values (1, 2)";
} else { // User the column c2 default value
   query = "insert into table_name (c1) values (1)";
}

Answer 4

In the stored procedure, you can set the parameter to have a default value itself, so that if you leave that parameter out of your application code, the procedure will substitute a default value.

@field_with_def_value int = -1,
etc...

If you want, you could set that default to the field's default value and be done with it. But it -might- be safer to test for that default value in the sproc and then conditionally call an INSERT that does the insert with or without that field's value. This way if the default is changed, you don't need to change your application or sproc code