SQL Server If statement woes

Question

Hi all,

I'm having some trouble with the following sproc

Create PROCEDURE GetMatchingUsers
@id int = NULL,
@lastName varchar(50) = NULL,
@firstName varchar(50) = NULL
AS
BEGIN

 SET NOCOUNT ON

 DECLARE @q nvarchar(4000),
 @paramlist  nvarchar(4000)  

    SELECT @q = 'SELECT Id
  , LastName
  , FirstName '
 SELECT @q = @q + 'FROM Users WHERE 1 = 1' 

 IF ISNULL(@id, '')  <> ''                                 
  SELECT @q = @q + ' AND Id = ' + Cast(@id as varchar)
 IF ISNULL(@lastName, '')  <> ''                                           
  SELECT @q = @q + ' AND LastName like ''' + @lastName + '%''' 
 IF ISNULL(@firstName, '')  <> ''                                           
  SELECT @q = @q + ' AND FirstName like ''' + @firstName + '%'''   

 SELECT @q = @q + ' ORDER BY LastName, FirstName '

 --PRINT @q

 SELECT @paramlist = '
  @id int = NULL,
  @lastName varchar(50) = NULL,
  @firstName varchar(50) = NULL'

 EXEC sp_executesql @q, @paramlist,                               
   @id,
   @lastName,
   @firstName

I was wonder why the following if statement is not considered true if i pass 0 as an id

IF ISNULL(@id, '')  <> ''                                 
    SELECT @q = @q + ' AND Id = ' + Cast(@id as varchar)

Thanks for any help

Answer 1

Zero is not the same thing as NULL. Null is more or less the absence of any value. Zero is a value.

If you want 0 to be a value that you can pass it to work the same as if you had passed in NULL (i.e. if you give it 0, don't do the select) then do this:

IF ISNULL(@id, 0)  <> 0                                 
    SELECT @q = @q + ' AND Id = ' + Cast(@id as varchar)

Answer 2

That is pretty weird - it probably has something to do with the fact that you are mixing up an int and a string literal. It seems more straightforward to do if @id is null or if @id is not null depending on your requirements

I reproduced this with a simple example (I changed <> to = to make the logic a little more obvious):

declare @id int
set @id = 0

if isnull(@id, '') = '' 
  print 'true'
else
  print 'false'

You would expect this to print 'false', but it prints 'true'. If you set the value of @id to 1, it then behaves as expected.

Answer 3

NULL means unknown, not zero. So

IF @id > 0

Should work. But I would stay away from building a string and rewrite it as:

SELECT Id 
  , LastName 
  , FirstName 
FROM Users
WHERE id = @id or
(@lastName is null or LastName like @lastName+'%') or
(@firstName is null or FirstName like @firstName+'%') 
ORDER BY LastName, FirstName 

Below is the same, but less self-documenting.

SELECT Id 
  , LastName 
  , FirstName 
FROM Users
WHERE id = @id or
LastName like @lastName+'%' or
FirstName like @firstName+'%' 
ORDER BY LastName, FirstName 

Answer 4

declare @id int 
set @id = 0     
if isnull(@id, '') = ''  
  print 'true'

This shouldn't surprise anybody, it's all documented in the product specifications:

• ISNULL is documented to return the type of the checked expression, not the replacement one. So isnull(@id, '') will return 0 as a type int.
• The comparison if 0='' will follow rules of Data Type Precedence and convert to the higher precendence type, in this case int.
• The string '' converted to an int value, equivalent to cast('' as int), is 0.

So the comparison is realy the same as writing if 0=0, which is, of course, true. q.e.d.