CODEWITHSUNDEEP
sqlsql-servert-sql
Joe Garry
Joe Garry

Reputation: 11

inner join on temp table unless it is empty

I want to do an inner join with a temp table but only if another variable is not NULL? For example I have a user table, and I pass in a string with userid's separated by semicolons. I read this into a temp table, and inner join against the user table, but only if the string of userid's is not null. Otherwise I want to return all the user rows.

stored proc has a variable passed in:

@UidList             varchar(max),

this is a string of user ids, separated by semicolons. This is turned into a temp table

DECLARE @List TABLE (UserUid uniqueidentifier NOT NULL)

then another function converts passed in string to table

IF @UidList IS NOT NULL
INSERT INTO @List(UserUid)
SELECT ItemUid
FROM dbo.AFN_ConvertUidListToTable(@UidList)

Then I join on users table

select * from users 
...
inner join @List on users.userid = @List.UserUid`

but if the passed in string @UidList is null then I want to match all rows in the users table.

Upvotes: 1

Views: 2231

Answers (4)

criticalfix
criticalfix

Reputation: 2870

The left join will pick up all records from the users table if @UidList IS NULL:

SELECT users.* 
FROM users
LEFT JOIN @List 
ON @List.UserUid = users.userid
WHERE @List.UserUid IS NOT NULL
OR @UidList IS NULL

But if @UidList IS NOT NULL, then we are looking for matches.

Upvotes: 0

bummi
bummi

Reputation: 27377

declare @tmp table (id int)

--insert into @tmp values (1)

Select * from a
where 
(
ID in (Select ID from @tmp)
or 
(Select Count(ID)  from @tmp)=0
)

http://sqlfiddle.com/#!3/a1a22/10

Upvotes: 1

criticalfix
criticalfix

Reputation: 2870

DECLARE @UserCount INTEGER

SELECT @UserCount = COUNT(1) FROM #UserIDList

IF @UserCount > 0
BEGIN
  SELECT UserTable.* FROM UserTable JOIN #UserIDList ON UserTable.ID = #UserIDList.ID
END
ELSE
BEGIN
  SELECT UserTable.* FROM UserTable
END

Upvotes: 0

chrisb
chrisb

Reputation: 2210

Surely this is just a case of looking at the userids string and branching?

IF @UserIds IS NULL
  select * from users
ELSE
  'stuff involving tempdb

Upvotes: 0

Related Questions