Dataset does not show fields with a procedure having temp table in it.

Question

Post reply

Dataset does not show fields with a procedure having temp table in it.

malavika.ramanathan

SSCrazy

Points: 2790
More actions
August 26, 2010 at 5:04 am

#226222

Hi All,
I have a procedure which calculates percentage. so i have two temp tables.
One to fetch the data from DB and the other to store the calculation.
finally my result set would be
select * from #temp
The same procedure when run with a table variable (@temp) , works fine and dataset also shows the fields.
I found this strange. will usage of temporary tables cause this problem?
Please suggest me.
here is a sample:
1st code does not work. the data set will be blank. The 2nd one works fine.
***************************************************************************************
create proc temptabletest
as
begin
create table #temp
(id int,
name varchar(20))
insert into #temp
select 1,'AAA' union all
select 2,'BBB' union all
select 3,'CCC' union all
select 4,'DDD'
select * from #temp
end
*****************************************************************************
create proc tablevariabletest
as
begin
declare @temp table
(id int,
name varchar(20))
insert into @temp
select 1,'AAA' union all
select 2,'BBB' union all
select 3,'CCC' union all
select 4,'DDD'
select * from @temp
end

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

i am able to get the result from both way using exec tablevariabletest(your sp)

----------
Ashish

Riskworks Mr or Mrs. 500 Points: 592 More actions · Answer 2

Works for me too. Although I would suggest to stay away from select * of course:

create proc temptabletest

as

begin

-- drop procedure temptabletest

IF object_id('tempdb..#temp') IS NOT NULL

DROP TABLE #temp

create table #temp

(IDint,

[name] varchar(20))

insert into #temp

select 1,'AAA' union all

select 2,'BBB' union all

select 3,'CCC' union all

select 4,'DDD'

select [id],[name]

from #temp

end

John A. Byrnes

malavika.ramanathan SSCrazy Points: 2790 More actions · Answer 3

Hi All,

I think i was not very clear. I am facing this problem in the dataset.

the standalone proc when executed works fine for #temp too.

One thing i found was, when i changed the command type to Query ,

and typed the proc name in the dataset, it is working.

I dont know what is the problem....

Can you suggest me something?

Daniel Bowlin SSC-Dedicated Points: 34566 More actions · Answer 4

I don't know why you are having a problem they way you have it, I have done this in SSRS several times. But if it works as a query, then make your query

EXEC StoredProcedureName