user default column value in INSERT stored procedure
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
S开发者_JAVA百科ITUATIONAL 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
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
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
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)";
}
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
精彩评论