SQL Server - check input parameter for null or zero

Question

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?

Mahmoud Gamal · Accepted Answer · 2013-05-16 06:24:01Z

3

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

edited May 16, 2013 at 6:24

answered May 16, 2013 at 6:08

Mahmoud Gamal

80.2k18 gold badges143 silver badges168 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user467947 Over a year ago

Thanks. That was quick and very helpful.

Damien_The_Unbeliever Over a year ago

Except that this code doesn't do the same job at all - if @someval is not 0 or NULL, this sets some_column to NULL (Did you intend to have ELSE @someval in your CASE expression?)

Mahmoud Gamal Over a year ago

@Damien_The_Unbeliever - Yes, you are right, because the default value for ELSE is NULL since he didn't provide one.

Andrey Gurinov · Accepted Answer · 2013-05-16 06:09:13Z

1

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

answered May 16, 2013 at 6:09

Andrey Gurinov

2,9051 gold badge23 silver badges23 bronze badges

Comments

Damien_The_Unbeliever · Accepted Answer · 2013-05-16 06:10:42Z

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.

Collectives™ on Stack Overflow

SQL Server - check input parameter for null or zero

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related