Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cipher
Starting Member
10 Posts |
 Posted - 2011-02-15 : 17:31:31
|
| We have built an INSERT stored procedure for a table with multiple fields. However, we're trying to find a way to allow the main INSERT statement to use the DEFAULT clause whenever a passed input parameter field is NULL.For example, here is a simple example of what we are trying to accomplish in the proc:CREATE PROC Table1Insert@ID int,@DateStamp DateTime NULLASINSERT INTO Table1 ( ID, DateStamp )VALUES( @ID, CASE WHEN @DateStamp IS NULL THEN DEFAULT ELSE DateStamp END )GOUnfortunately, the DEFAULT clause is invalid in the CASE statement above. Is there a way we can control whether a passed parameter or a field DEFAULT is used by an INSERT for a particular field?Thanks |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2011-02-15 : 18:42:30
|
| You have to pass in the Default value if your column doesn't have a default value defined on it.VALUES (@id,ISNULL(@datestamp,<default value here>)JimEveryday I learn something that somebody else already knew |
 |
|
|
cipher
Starting Member
10 Posts |
Posted - 2011-02-15 : 22:15:26
|
| HI Jim,The DateStamp field does have a default value assigned. Therefore, the following code works...INSERT INTO Table1 (ID, DateStamp)VALUES(@ID, DEFAULT)However, we want to programmatically control whether we're assigning this field to the incoming parameter or the DEFAULT keyword, which is why we tried the CASE statement in the first example. |
|
|
|
MIK_2008
Master Smack Fu Yak Hacker
1054 Posts |
Posted - 2011-02-16 : 00:09:52
|
quote:
Originally posted by cipher
HI Jim,The DateStamp field does have a default value assigned.
In this case you do not pass any value, SQL server will assign the default automatically .e.g.INSERT INTO Table1 (ID)VALUES(@ID) |
|
|
|
|
|
|
|
|
DEFAULT clause not allowed in CASE statement?