Table Variable and Checking for Null in SQL Server

Question

I have the following query.

CREATE TYPE [dbo].[DeptIdList] AS TABLE(
    [DepartmentId] [smallint] NULL
)
GO
set nocount on

declare @department_ids DeptIdList
declare @tableContainsRecords int = 0

insert into @department_ids
values
(5), (6), (7), (8), (9)

select d.DepartmentID, d.Name, d.GroupName
  from HumanResources.Department as d
  right outer join @department_ids as di on
    d.DepartmentID = di.DepartmentID

I want to select specific departments if the @department_ids contains rows; if not the SELECT should return all records in the department table.

What is the easiest and optimal way to do this?

Lamak · Accepted Answer · 2012-08-22 15:45:18Z

1

This should work:

SELECT d.DepartmentID, d.Name, d.GroupName
FROM HumanResources.Department d
WHERE DepartmentID IN (SELECT DepartmentID FROM @department_ids)
OR (SELECT COUNT(*) FROM @department_ids) = 0

answered Aug 22, 2012 at 15:45

Lamak

70.8k12 gold badges118 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Table Variable and Checking for Null in SQL Server

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related