CODEWITHSUNDEEP
sql-servercase
acedanger
acedanger

Reputation: 1206

Setting a value via a CASE statement

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

Upvotes: 0

Views: 2228

Answers (1)

Kaf
Kaf

Reputation: 33809

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

Upvotes: 2

Related Questions