results from a SP into a #table ?

Question

results from a SP into a #table ?

SQL_path

SSCrazy

Points: 2810
More actions
September 11, 2012 at 8:58 am

#261718

Hi,
I am getting empty result set while trying to insert results from a proc into a #table unless I use @Item_type=@Item_Type instead of just @Item_type. Something along the lines below. curious to know why it is behaving like this.
it worked fine before and only added @Item_type varchar(20) in spgetOrders. Tried open rowset but it is not allowed at database level
Any ideas please ? thank you !!
Create table #temp
(C1 varchar(20) Not null,
C2 varchar(20) not null,
C3 varchar(20) not null,
C4 varchar(20) ,
C5 varchar(20),
C6 varchar(20),
C7 varchar(20)
)
Insert into #Temp values
(C1,
C2,
C3,
C4,
c5,
c6,
C7)
EXEC [dbo].[spgetOrders]
@StartDate,
@EndDate,
@Item ,
@Item_type = @Item_type --- gives results.
--,@Item_type --- does not give result
select * from #temp values
drop table #temp

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Would help to see the stored procedure code and how you are actually calling it. Just based on your initial post, hard to tell you what is happening.

SQL_path SSCrazy Points: 2810 More actions · Answer 2

Thanks for the reply. It is actually very long complicated code. I am trying to condense it and will try to post more without violating the policy ...sigh!!

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

SQL_path (9/11/2012)
Thanks for the reply. It is actually very long complicated code. I am trying to condense it and will try to post more without violating the policy ...sigh!!

Start by posting the Header (the CREATE PROC name and parameters followed by "AS"). That's where the problem probably is anyway.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

SQL_path SSCrazy Points: 2810 More actions · Answer 4

[/quote]

Start by posting the Header (the CREATE PROC name and parameters followed by "AS"). That's where the problem probably is anyway.

[/quote]

Thank you Barry !! That was it.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Heh. Well, glad I could help. 🙂

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

SQL_path (9/17/2012)

Start by posting the Header (the CREATE PROC name and parameters followed by "AS"). That's where the problem probably is anyway.

[/quote]

Thank you Barry !! That was it.[/quote]

So what did you do to fix it?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

I'm guessing the parameter order was different. But it would be good to know for sure.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]