0

I have a table that consists of some code-cells (for indexing purposes), and one "value" cell (where the data I am interested in lives), similar to the following:

 column:      datatype:

  code1         int
  code2         int
  code3         int
  code4         int
  attributes    varchar(max)

I am checking against some input codes in the stored procedure that adds to this table, and the constraint for adding is that my INPUT codes (@code1, @code2, @code3, @code4) can either equal some value OR they may be null, but are the same as those in some matching row of the table.

What is the best way to say the following statement:

 SET @targetAttributesCell = (SELECT attributes FROM MyTable
                               WHERE (code1 = @code1)
                                 AND (code2 = @code2)
                                 AND (code3 = @code3)
                                 AND (code4 = @code4)); <-- where code1/@code1 'IS NULL' or = '[some integer]'?

Thanks in advance. Please let me know if I need to be more clear.

Rachael
  • 1,965
  • 4
  • 29
  • 55
  • 3
    If you're not certain that you're being clear, give example data along with the behaviour/results that you expect. – MatBailie Nov 15 '12 at 17:27

3 Answers3

3

Here you go:

WHERE ISNULL(Code4,SomeInteger)=SomeInteger

If Code4 is null, it'll match the integer. If Code4 matches the integer, it'll match the integer. If Code4 is any other integer, it won't match.

EDIT

Do you even need this check? Are there NULL values in your table? If so, then the NULL parameters would just match up.

Jim
  • 3,482
  • 22
  • 18
  • Ooh. I like this, @Jim . This looks better than my solution. I had a feeling ISNULL would provide me with the correct solution. Checking it out now. Yes, btw, the NULL values are in the table but for whatever reason it isn't working out when I do the check. – Rachael Nov 15 '12 at 18:43
  • 1
    This worked great. What I did was: `WHERE ISNULL(code1, 0) = ISNULL(@code1, 0)` Thanks so much for reading my whole question thoroughly. :) :) – Rachael Nov 15 '12 at 18:47
  • To elegantly check for nulls, you could also do `WHERE Code4 Is Null` (ref: http://stackoverflow.com/questions/16080139/null-or-empty-check-for-a-string-variable) – Hamman Samuel Jan 16 '14 at 19:26
0

In my case, it works. Especially if you treat zero as a value.

ISNULL(cast(@FirstInt as varchar),'')<>ISNULL(cast(@SecondInt as varchar),'')

ni3.net
  • 377
  • 4
  • 14
-1

Try this...

SET @targetAttributesCell = (SELECT attributes FROM MyTable
                               WHERE ( @code1 IS NOT NULL and code1 = @code1)
                                 AND (@code2 IS NOT NULL and code2 = @code2)
                                 AND (@code3 IS NOT NULL and code3 = @code3)
                                 AND (@code4 IS NOT NULL and code4 = @code4)); <-- where code1/@co
Bull
  • 701
  • 1
  • 6
  • 13
  • 1
    That's the wrong logic, as far as I understand it... Didn't the op want `(code1 IS NULL OR code1 = @code1)`? – MatBailie Nov 15 '12 at 17:26
  • As @Dems comments. If the `code1 = @code1` is true then the `@code1 IS NOT NULL` check is redundant. – ypercubeᵀᴹ Nov 15 '12 at 17:28
  • Or possibly even `(code1 IS NULL OR @code1 IS NULL OR code1 = @code1)`? – MatBailie Nov 15 '12 at 17:28
  • Read the heading guys...before marking this down. UB3571 is asking how to check whether ...these codes are not null and they are equal to an integer value. i can't understand why u thing this logic is wrong. UB3571 please try and let us know if this is what you wanted. – Bull Nov 15 '12 at 17:34
  • 1
    Errr, the title has the word ***OR*** in it *(as opposed to AND)*... `SQL Server: Elegant way to check if value “IS NULL” or “= integer value”` As does the body of the question... – MatBailie Nov 15 '12 at 17:41
  • @Bull: I have not downvoted you. But you can check easily that `( @code1 IS NOT NULL and code1 = @code1)` is **equivalent** to `(code1 = @code1)`. Always, nulls or no nulls. – ypercubeᵀᴹ Nov 15 '12 at 18:06