November 29, 2006 at 7:00 am
i am doing an insert and i have condition on each field whether to
be filled or not. i don't want to write many if statements then insert,
i thought there was smthg like
if isnull(@variable) ? null : @anothervariable , but it's not working
insert into mytable(f1,f2,f3)
values(@var1,
getdate(),
ISNULL(@var3) ? NULL : @var3)
i.e. if @var3 is null, keep the field null, else put @var3 in it
November 29, 2006 at 7:02 am
November 30, 2006 at 5:25 am
I'm not sure I understand the question correctly...
Why would you want to do this : "if @var3 is NULL, keep the column value NULL, else put @var3 in it"? If that is what you want to do, and it is during INSERT, just use @var3 without any IFs. If @var3 is NULL, you will insert NULL.
If you need to use some other value instead of NULL, you have this for example this possibility (if @var1 is NULL, insert @var2; otherwise insert @var1) :
INSERT INTO mytable(col1)
SELECT ISNULL(@var1, @var2)
COALESCE allows even more attempts, if also the second variable is also NULL you try another etc.
INSERT INTO mytable(col1)
SELECT COALESCE(@var1, @var2, @var3, @var4)
Oh... and of course if you want to do that with the VALUES clause:
INSERT INTO mytable(col1) VALUES(ISNULL(@col1,@col2))
November 30, 2006 at 5:03 pm
I think what he is looking for is:
insert into mytable(f1,f2,f3)
values(@var1,
getdate(),
Case When @Var3 Is Not Null Then @Var4 Else Null End)
You actually could leave off the "Else Null" part because if no else condition is specified, it defaults to null. So these two statements are equivalent:
1. Case When @Var3 Is Not Null Then @Var4 Else Null End
2. Case When @Var3 Is Not Null Then @Var4 End
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply