T-SQL A question about inner join table variable

Question

Hi, in my stored procedure I have a table variable contains rows ID. The are 2 scenarios - that table variable is empty and not.

declare @IDTable as table
(
  number NUMERIC(18,0)
)

In the main query, I join that table:

inner join @IDTable tab on (tab.number = csr.id)

BUT:

as we know how inner join works, I need that my query returns some rows:

when @IDTable is empty

OR

return ONLY rows that exist in @IDTable

I tried also with LEFT join but it doesn't work. Any ideas how to solve it ?

Answer 1

If `@IDTable' is empty then what rows do you return? Do you just ignore the Join on to the table?

I'm not sure I get what you're trying to do but this might be easier.

if (Select Count(*) From @IDTable) == 0
    begin
    -- do a SELECT that doesn't join on to the @IDTable

     end
else
    begin
    -- do a SELECT that joins on to @IDTable
    end

Answer 2

It is not optimal, but it works:

declare @z table
  (
    id int
  )
  --insert @z values(2)

  select * from  somTable n 
    left join @z z on (z.id = n.id)
  where NOT exists(select 1 from @z) or (z.id is not null)