SQL Server If statement woes

Question

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

Remus Rusanu · Accepted Answer · 2010-01-08 21:48:09Z

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.

Ray · Accepted Answer · 2010-01-08 20:55:21Z

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.

Stephen Wrighton · Accepted Answer · 2010-01-08 20:33:33Z

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)

I actually want it to concatenate the string if the id passed in is 0. The only time i don't want it to go in to the block is if the id is not passed in at all.
then change the 0 up above to -1 (i.e.: ISNULL(@Id,-1) <> -1

JBrooks · Accepted Answer · 2010-01-08 20:57:00Z

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

Collectives™ on Stack Overflow

SQL Server If statement woes

4 Answers 4

Comments

Comments

2 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Related