Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

results from a SP into a #table ? Expand / Collapse
Author
Message
Posted Tuesday, September 11, 2012 8:58 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:35 AM
Points: 158, Visits: 336
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


Post #1357493
Posted Tuesday, September 11, 2012 9:05 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:36 PM
Points: 20,738, Visits: 32,522
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1357501
Posted Tuesday, September 11, 2012 9:17 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:35 AM
Points: 158, Visits: 336
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!!
Post #1357509
Posted Tuesday, September 11, 2012 10:26 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 9,294, Visits: 9,484
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1357571
Posted Monday, September 17, 2012 7:21 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 8:35 AM
Points: 158, Visits: 336
[/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.
Post #1360185
Posted Thursday, September 20, 2012 2:37 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 9,294, Visits: 9,484
Heh. Well, glad I could help.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1362322
Posted Thursday, September 20, 2012 3:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:38 PM
Points: 35,371, Visits: 31,912
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1362348
Posted Friday, September 21, 2012 11:51 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 11:52 AM
Points: 9,294, Visits: 9,484
I'm guessing the parameter order was different. But it would be good to know for sure.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #1362881
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse