sql server programming: In my stored procedure I need to put data I get from dynamically built
SELECT query into particular temporary table.
i.e.
EXEC ('SELECT 1 AS [number] INTO #t')
SELECT * FROM #t
Of course this doesn't work because of scope of [#t]
Unfortunatelly I don't know exact structure of [#t] in advance, so I
can't create it explicitly.
Any ideas?

Rad
SELECT * INTO #t FROM what?



[quoted text, click to view]

You haven't defined where you want to select from. If you do that, it should
work.

[quoted text, click to view]

But you would need to encapsulate the select statement within the exec
too... eg.

EXEC ('SELECT 1 AS [number] INTO #t from TABLE
SELECT * FROM #t')

[quoted text, click to view]

Rad (lytchev@gmail.com) writes:
[quoted text, click to view]

Yes, back out and look for an entirely different solution.

The precise reason the above fails is that a temp table disappears when
the scope it was created in exits. You can work around this with a
permanent table or a global temp table (one with two ##). But then you
must take precautions that the name does not collide between parallel
invocations of the code.

And furthermore, all your further work with this table will be through
dynamic SQL, and it will be extremely painful. You are really a lot lot
better off doing this exercise from a client language, which is far
more apt at string manipulation than T-SQL.

And, of course, the fact that you don't know your table structure
in advance is highly suspicious. What are you really trying to accomplish?
The one case I can think of is a dynamic crosstab. In such case, look
at http://www.rac4sql.net/ for a third-party product that can save you
a lot of headache.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Rad
As you said you do not know how many columns you are getting from
very-long-dynamically-built-statement, am I right?

In that case use SELECT * INTO #Test FROM
very-long-dynamically-built-statement

Now that you have created a new temporary table you may alter the table and
add another column as SUSER_SID() by deafult

I hope you take care of preventing from sql injection afftected your
database








[quoted text, click to view]

Omnibuzz (Omnibuzz@discussions.microsoft.com) writes:
[quoted text, click to view]

Fool-proof for a unique name? Yss, it should br safe. Even

SELECT @tblname = #mytemp + ltrim(str(@@spid))

should do.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Regular table, global ##Temp table. -Both subject to interference from other
users.

IF you could define the #Temp table [#t] before calling the dynamic sql, you
could capture the output. But with your specification, it is not possible to
use a local #Temp table.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous


[quoted text, click to view]

[quoted text, click to view]
SELECT * INTO #t FROM [very-long-dynamically-built-statement]

Let's say

CREATE PROCEDURE [sp_select]
@nStyle int = 0
AS
BEGIN
DECLARE @cQuery nvarchar(1000)
IF (@nStyle = 0)
SET @cQuery = 'SELECT SUSER_SNAME()'
ELSE
SET @cQuery = 'SELECT SUSER_SID()'

SET @cQuery = @cQuery + ' AS [column] INTO #t'

IF (OBJECT_ID('tempdb..#t') IS NOT NULL) OR (OBJECT_ID('#t') IS NOT
NULL)
DROP TABLE #t
EXEC @cQuery
SELECT * FROM #t
END
GO

And I can't just "SELECT" because client-side application may put some
values into the same [#t] table and call another procedure to save that
values into actual tables.

Kind of Enterprise Engine, you know :)

[quoted text, click to view]