Local Temporary Tables

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,
EricBeringer 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|||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" <individual_news@.nibsworld.com> wrote in message
news:2tqnlcF21lmapU2@.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|||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" <borden_eric@.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" <individual_news@.nibsworld.com> wrote in message
> news:2tqnlcF21lmapU2@.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