Setting a value via a CASE statement

Question

I am new to t-sql programming. I have a background in Java, Javascript and numerous other programming languages. I'm trying to set the value of the @notes variable. My code is below.

Basically, what I want to do is set the @notes variable if the @minBatch and @maxBatch are not null. I added a print statement to ensure that @minBatch and @maxBatch are the values I'm passing in.

In short, what's wrong with the following code?

    set @notes =
          case 
            when @minBatch != null and @maxBatch != null then
                case 
                  when @minBatch != @maxBatch then 
                    'Start batch: ' + cast(@minBatch as varchar(8)) + ' End batch: ' + cast(@maxBatch as varchar(8))
                  else 
                    'Batch ' + cast(@minBatch as varchar(8))
                end
            else
              null
          end

Kaf Kaf · Accepted Answer · 2012-12-11T19:04:19

Use is null instead of =null

SELECT @notes = 
     CASE 
         WHEN (@minBatch + @maxBatch) is not null --Comment: if any null result is null 
              THEN
                 CASE @minBatch WHEN @maxBatch --@min or @max still can be null
                      THEN 'Batch ' + cast(ISNULL(@minBatch,0) as varchar(8))
                      ELSE 'Start batch: ' + 
                            cast(ISNULL(@minBatch,0) as varchar(8)) + ' End batch: ' + 
                            cast(ISNULL(@maxBatch,0) as varchar(8))
                 END
         ELSE null
     END

EDIT: simplified using (@minBatch + @maxBatch) is not null

--Comment: if any null result is null

Setting a value via a CASE statement

1 Answers