CODEWITHSUNDEEP
sqlsql-server
user1617413
user1617413

Reputation: 1

Table Variable and Checking for Null in SQL Server

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?

Upvotes: 0

Views: 978

Answers (1)

Lamak
Lamak

Reputation: 70668

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

Upvotes: 1

Related Questions