CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user467947
user467947

Reputation: 291

SQL Server - check input parameter for null or zero

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

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

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

Damien_The_Unbeliever
Damien_The_Unbeliever

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

Andrey Gurinov
Andrey Gurinov

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

Related Questions