check if temp table is empty?

[slybitz]

I created a temp table in my procedure that i insert data into in the initial part of my procedure. After I insert the selected data into the temp table I'm trying to find out how to determine if the temp table is empty or not. If it is not empty it will begin emailing me the data (which i already created the code for) but if it is empty then it will do nothing. So I just can't figure out how to determine whether or not the temp table is null or not and then have it continue the procedure depending on if the temp table is null or not. This is what i tried so far but with no suceess (#NoMasKey is my temp table):

(here i insert selected data into #NoMasKey)
IF 

   object_id('#NoMasKey') is not null

BEGIN

(here is where i have the rest of the code i want to complete if the table is not null (empty)

Any ideas as to why this doesn't work or what I should? Does this make sense? Thanks for your time!!

 

[SQLDenis]

Declare @intTempTableCount int
select @intTempTableCount = count(*) from #NoMasKey'

if @intTempTableCount > 0
begin

..
..
..
end

http://sqlservercode.blogspot.com/

 

[SQLDenis]

select @intTempTableCount = count(*) from #NoMasKey'
Should be
select @intTempTableCount = count(*) from #NoMasKey

no tickmark at the end

http://sqlservercode.blogspot.com/

 

[vongrunt]

> Any ideas as to why this doesn't work or what I should?

OBJECT_ID() returns nonnull value if object exists. It won't tell you whether table is empty or not. Plus it works on current DB by default; try object_id('tempdb..#NoMasKey').

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[jbenson001]

Just do a Select Count(*) from #NoMasKey. Even if no rows are inserted, the table is still created.

Jim

 

[SQLDenis]

Another approach is

select * into #NoMasKey
from realTable where field = something

IF object_id('#NoMasKey') is not null should work in this case because your temp ttable won't be created unless there is data

here is a quick test


use pubs
go
select * into #NoMasKey
from publishers where 1=0

IF object_id('#NoMasKey') is not null print 'not null' else print 'null'

http://sqlservercode.blogspot.com/

 

[vongrunt]

And after that...

select * from #NoMasKey

?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[slybitz]

Wow, VERY helpful! Thank you all so much. My problem is resolved in more ways than one. Stars for you all!