CODEWITHSUNDEEP
sqlstored-procedures
Ruby
Ruby

Reputation: 969

Stored Procedure With Bit Parameter

One of the parameters is of Bit datatype. Though the values in DB table are only 0's and 1's, when sending the parameter from interface, it can also be a null. The foll. query gives 0 rows. I suspect the problem could be with this bit parameter.

ALTER PROCEDURE GETDATA (@username  VARCHAR(50),
                         @profileid UNIQUEIDENTIFIER,
                         @status    BIT)
AS
  BEGIN
      IF @username = ''
        SET @username = NULL
      ELSE
        SET @username = '%' + @username + '%'

      IF @profileid = '00000000-0000-0000-0000-000000000000'
        SET @profileid = NULL

      SELECT u.*
      FROM   tbluser u
      WHERE  u.deleted = 0
             AND ( u.username like @username
                    OR u.username IS NULL )
             AND ( u.profileid = @profileid
                    OR u.profileid IS NULL )
             AND ( u.status = @status
                    OR ( @status IS NULL
                         AND u.status IS NULL ) )
  END

--execute GetData null,null,null

Expected Output

UserId EmpId Username profileid status
--------------------------------------
 1      101   user1     10       1

Upvotes: 1

Views: 11242

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20804

There are a lot of problems with your procedure. The first one is this combination:

IF @username = ''
    SET @username = NULL
ELSE
    SET @username = '%' + @username + '%'

followed by 

 u.username = @username

If a null value is sent, this code will execute:

SET @username = '%' + @username + '%'

That will make @username = null, and the query will crash because you can't have

where somefield = null

You have to have 

where somefield is null

There is a similar problem with passing an empty string. Now, suppose a value of 'fred' is passed as the username. Once again, this executes:

SET @username = '%' + @username + '%'

and this:

 u.username = @username

becomes 

 u.username = '%fred%'

You probably wanted the word like instead of an equal sign there.

For the @status variable, if your db does not have null values, don't look for any. If you get one passed to the proc, you probably want to use dynamic sql, or a case construct. Something like this should work.

and u.status = case when @status is not null then @status
else u.status end

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270081

The logic for the status column is different from the other two and will always fail when you pass in NULL (because you say the values in the database are always 0 and 1).

Do you really want this logic?

  WHERE  u.deleted = 0
         AND ( u.username = @username OR @username IS NULL )
         AND ( u.profileid = @profileid OR @profileid IS NULL )
         AND ( u.status = @status OR @status IS NULL )

This will treat NULL input parameters as not doing any filtering on the column.

Upvotes: 1

Related Questions