Local Temporary Tables

[Beringer]

I have created a local temporary table (#Temp) and placed data in it.

When I do:
SELECT * #Temp

The result is a set of rows with no columns.

The SELECT statement is in the same procedure as the creation of #Temp; so I
don't believe this is a scoping issue. Does that make sense?

Thank you in advance,
Eric

[nib]

Beringer wrote:

I have created a local temporary table (#Temp) and placed data in it.

When I do:
SELECT * #Temp

The result is a set of rows with no columns.

The SELECT statement is in the same procedure as the creation of #Temp; so I
don't believe this is a scoping issue. Does that make sense?

Thank you in advance,
Eric

No, it doesn't make sense. First, your code above is invalid SQL.
Second, you don't provide any DDL or sample data for us to reproduce the
problem.

Zach

[Beringer]

Please be patient with me. I'm learning how to do this stuff by myelf and
just begining! :)

At anyrate here is an example:
ALTER PROCEDURE proc1

AS

SET NOCOUNT ON

CREATE TABLE #TempTable(my_text CHAR(10))

INSERT INTO #TempTable(my_text) VALUES ('test')

INSERT INTO #TempTable(my_text) VALUES ('test2')

INSERT INTO #TempTable(my_text) VALUES ('test3')

SELECT * FROM #TempTable

The result (visually in Access 2002, in datasheet view) after executing the
procedure is simply a row header with three rows and no columns. Of note,
if the SET NOCOUNT ON is commented out there is nothing.

Thanks again,

Eric

"nib" <in*************@nibsworld.com> wrote in message
news:2t*************@uni-berlin.de...

Beringer wrote:

I have created a local temporary table (#Temp) and placed data in it.

When I do:
SELECT * #Temp

The result is a set of rows with no columns.

The SELECT statement is in the same procedure as the creation of #Temp;
so I don't believe this is a scoping issue. Does that make sense?

Thank you in advance,
Eric

No, it doesn't make sense. First, your code above is invalid SQL. Second,
you don't provide any DDL or sample data for us to reproduce the problem.

Zach

[Barry Young]

I do similar stored procedures like this all the time. Except I create the
SP via Enterprise Manager and Check Syntax etc . Have you tried executing
this from Query Analyzer just to see if it works? Or just create a stored
procedure in Enterprise Manager and EXECUTE it from Query Analyzer...

The column in your example should have one column heading and three rows of
data (if I am reading it correctly).

Is it generating any errors?
Barry
"Beringer" <bo*********@invalid.com> wrote in message
news:l1Xdd.56381$kz3.16039@fed1read02...

Please be patient with me. I'm learning how to do this stuff by myelf and
just begining! :)

At anyrate here is an example:
ALTER PROCEDURE proc1

AS

SET NOCOUNT ON

CREATE TABLE #TempTable(my_text CHAR(10))

INSERT INTO #TempTable(my_text) VALUES ('test')

INSERT INTO #TempTable(my_text) VALUES ('test2')

INSERT INTO #TempTable(my_text) VALUES ('test3')

SELECT * FROM #TempTable

The result (visually in Access 2002, in datasheet view) after executing
the procedure is simply a row header with three rows and no columns. Of
note, if the SET NOCOUNT ON is commented out there is nothing.

Thanks again,

Eric

"nib" <in*************@nibsworld.com> wrote in message
news:2t*************@uni-berlin.de...

Beringer wrote:

I have created a local temporary table (#Temp) and placed data in it.

When I do:
SELECT * #Temp

The result is a set of rows with no columns.

The SELECT statement is in the same procedure as the creation of #Temp;
so I don't believe this is a scoping issue. Does that make sense?

Thank you in advance,
Eric

No, it doesn't make sense. First, your code above is invalid SQL. Second,
you don't provide any DDL or sample data for us to reproduce the problem.

Zach