Oh - use this:

if
(exists (select name from tempdb..sysobjects where name like '#test%' and xtype = 'u'))
begin
if ((select count(*) from #test) > 0)
print 'something'
end

- the temp table is stored in tempDB and takes on an improbable name, so the like operator is your best bet.
- the nested Ifs are necessary because SQL server is not clever enough to stop testing after the first proposition of an AND pair is proved false, so if you simply AND the two tests, you will get an error when the table does not exist.

> -----Original Message-----
> From: Aaron Dyer [mailto:mssqldba-ezmlmshield-
> x28492214.[Email address protected]
> Sent: Tuesday, May 31, 2005 9:13 AM
> To: LazyDBA Discussion
> Subject: RE: proc's result set to a temporary table.
>
> I have had success at doing a SELECT INTO within a stored procedure to
> create a table that does not disappear when the sp finishes. The key is
> to insert the code that checks for the table's existence and drop it if
> it does, then recreate it with the new results.
>
> -----Original Message-----
> From: Juan Vera
> [mailto:mssqldba-ezmlmshield-x74940637.[Email address protected]
> Sent: Tuesday, May 31, 2005 11:06 AM
> To: LazyDBA Discussion
> Subject: RE: proc's result set to a temporary table.
>
> Because the stored procedure will clean up its own temporary tables,
> what you need to do is create the temporary table outside the stored
> procedure & then have the stored procedure insert into it instead of
> selecting into...
>
> Best,
>
> Juan CristiÂn
>
> > -----Original Message-----
> > From: Gregorev Yuriy [mailto:mssqldba-ezmlmshield-
> > x2270413.[Email address protected]
> > Sent: Tuesday, May 31, 2005 8:53 AM
> > To: LazyDBA Discussion
> > Subject: proc's result set to a temporary table.
> >
> > Is it possible to have result set of stored procedure into temporary
> > table?
> > OR
> > If I need only one column of the whole result set returned by a stored
> > procedure how can I have it?
> >
> > Provided, of course, that altering or recreating the stored procedure
> is
> > not an option.
> >
> > Thank you in advance,
> > Yuriy
> >
> >
> > ---------------------------------------------------------------------
> > TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> > Website : http://www.LazyDBA.com
> > To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>
>
>
>
>
> ---------------------------------------------------------------------
> TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
> Website : http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html


MS Sql Server LazyDBA home page