1

I have this table structure and am attempting to return the two rows where loglevel is null. How should the query be written in order to return this?

Based off the sample DDL Below I want 2 rows for names returned:

Pink Pig and Green Glove

  Declare @stuff Table
  (name varchar(50), vendorname varchar(100), vendorid varchar(10), loglevel varchar(10))
  Insert Into @stuff (name, vendorname, vendorid, loglevel) Values
  ('Pink Pig', 'Vendor 1', '214ac', NULL), ('Red Hat', 'Vendor 1', '214ac', '3')
  ,('Blue Sox', 'Vendor 1', '214ac', '3'), ('Green Glove', 'Vendor 1', '214ac', NULL)
  ,('Green Lantern', 'Vendor 2', 'Er23', '1'), ('Grey Goose', 'Vendor 2', 'Er23', '1')
  ,('Fox Fire', 'Vendor 2', 'Er23', NULL), ('Flyfish', 'Vendor 2', 'Er23', NULL)

  Declare @vendorid varchar(10), @loglevel varchar(10)
  Set @vendorid = '214ac';
  Set @loglevel = NULL

  Select * FROM @stuff
  WHERE vendorid = @vendorID
  AND loglevel = @loglevel
  • Will it be Variable Table in your scenario? – Paweł Tajs Oct 24 '16 at 14:08
1

You can handle this scenario by dynamic SQL but as @Marco mentioned, you should consider to use default value there (for example empty string). All functions which apply loglevel column prevent SQL Server from using indexes.

Dynamic SQL, which can solve this problem (if you need to stick with Variable Table, you should follow Martin Smith solution):

IF OBJECT_ID('tempdb..#stuff') IS NOT NULL
    DROP TABLE #stuff

CREATE TABLE #stuff (name varchar(50), vendorname varchar(100), vendorid varchar(10), loglevel varchar(10))

INSERT INTO #stuff (name, vendorname, vendorid, loglevel) 
SELECT * FROM (VALUES
('Pink Pig', 'Vendor 1', '214ac', NULL), ('Red Hat', 'Vendor 1', '214ac', '3')
,('Blue Sox', 'Vendor 1', '214ac', '3'), ('Green Glove', 'Vendor 1', '214ac', NULL)
,('Green Lantern', 'Vendor 2', 'Er23', '1'), ('Grey Goose', 'Vendor 2', 'Er23', '1')
,('Fox Fire', 'Vendor 2', 'Er23', NULL), ('Flyfish', 'Vendor 2', 'Er23', NULL)) V(name, vendorname, vendorid, loglevel) 

DECLARE @vendorid VARCHAR(10) = '214ac'
        ,@loglevel VARCHAR(10) = NULL
        ,@sql NVARCHAR(MAX)

SET @sql = 'SELECT * FROM #stuff WHERE vendorid = @vendorID'
IF @loglevel IS NULL
    SET @sql += ' AND loglevel IS NULL'
ELSE
    SET @sql += ' AND loglevel = @loglevel'

EXEC sp_executesql @sql, N'@vendorID VARCHAR(10), @loglevel VARCHAR(10)', @vendorid, @loglevel
1

Try this.

Select * FROM @stuff
  WHERE vendorid = @vendorID
  AND loglevel Is Null
  • Is it possible to do such using a variable? – SmallFries BigGuys Oct 24 '16 at 13:42
  • 1
    @SmallFriesBigGuys Why would you use a variable if you always want to check if it's NULL (as you say in your question)? – Philᵀᴹ Oct 24 '16 at 13:53
  • 1
    @SmallFriesBigGuys Do you mean you want WHERE ((loglevel = @loglevel) OR (loglevel IS NULL AND @loglevel IS NULL)) ? – ypercubeᵀᴹ Oct 24 '16 at 15:05
0

NULL means undefined and is a special value. You can only compare it with IS NULL or IS NOT NULL. If you want to use it with = then you need to use something like ISNULL(loglevel, '0')=@loglevel or COALESCE(logLevel, '0')=@loglevel where @loglevel has the value '0' instead of NULL.

Consider to give it a default value instead of NULL in your table to prevent this.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.