Reputation: 291
I have a stored procedure for sql server 2008 like this:
create procedure test_proc
@someval int,
@id int
as
update some_table
set some_column = ISNULL(@someval, some_column)
where id = @id
go
If the parameter @someval
is NULL
, this SP will just use the existing value in some_column
.
Now I want to change this behaviour such that if value for @someval
is 0
, a NULL
is stored in some_column
otherwise it behave just the way it is doing now.
So I am looking for something like:
if @someval == 0
set some_column = NULL
else
set some_column = ISNULL(@someval, some_column)
I don't have the option to create a varchar @sql variable and call sq_executesql on it (at least that is the last thing I want to do). Any suggestions on how to go about doing this?
Upvotes: 0
Views: 3669
Reputation: 79979
You can do this using the CASE
expression. Something like this:
update some_table
set some_column = CASE WHEN @someval = 0 THEN NULL
WHEN @someval IS NULL THEN somcolumn
ELSE @someval -- the default is null if you didn't
-- specified one
END
where id = @id
Upvotes: 3
Reputation: 239824
I think it's a really bad idea - I'd suggest that if someone wants to store a NULL
, they really shouldn't have to pass some other magical value to cause it to happen. However, let's show how it can be done:
update some_table
set some_column = CASE WHEN @someVal = 0 THEN NULL ELSE ISNULL(@someval, some_column) END
where id = @id
Given the simplicity of the stored procedure in your question, of course, the whole matter can be cleared up by not calling the stored procedure if you don't want to alter some_column
. I'd imagine that your real procedure is more complex. Instead, what I'd do is have:
create procedure test_proc
@someval int,
@someval_specified bit,
@id int
as
update some_table
set some_column = CASE WHEN @someval_specified = 1 THEN @someval ELSE some_column END
where id = @id
And now NULL
means NULL
, 0
means 0
, etc.
Upvotes: 0
Reputation: 2895
something like this?
create procedure test_proc
@someval int,
@id int
as
update some_table
set some_column = CASE
WHEN @someval = 0 THEN NULL
ELSE ISNULL(@someval, some_column) END
where id = @id
go
Upvotes: 1