THAT requires the user/executor of the SP to have create table privileges(or
dbo) if selecting into a non temp table(real tablename). Not allowed in
most production environments.

If you allow that then a simple

If exists (check for table) drop table
Else select into

Should work.


Thanks,
Ralph W. Davis
*********************************************************
*** CORPORATE DBA group - Houston ***
*********************************************************

-----Original Message-----
From: Aaron Dyer
[mailto:mssqldba-ezmlmshield-x28492214.[Email address protected]
Sent: Tuesday, May 31, 2005 11: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

Confidentiality Note: The information contained in this message, and any
attachments, may contain confidential and/or privileged material. It is intended
solely for the person or entity to which it is addressed. Any review,
retransmission, dissemination, or taking of any action in reliance upon this
information by persons or entities other than the intended recipient is
prohibited. If you received this in error, please contact the sender and delete
the material from any computer.


MS Sql Server LazyDBA home page