January 31, 2011 at 5:48 pm
I am trying to use a Cursor to repeat a block of code with a different parameter value for each instance. I tried a temp table to do the same thing, but came up with the same problem. I get one rowset for each parameter value, instead of one rowset with a row for each parameter.
What I want to get:
Rowset: Parm1 Data1
Parm2 Data 2
...
What I am getting is:
Rowset 1: Parm1 Data1
Rowset 2: Parm2 Data2
I do not have authority to create a stored procedure, so I am trying to to this strictly with cursors or temporary tables.
Here is the code:
Declare @DC char(2)
Declare @DC_NAME varchar(50)
Declare DC_LIST cursor for
select stg.DistCenter
,dc.dist_center + ' ' + dc.dist_center_name as DC_NAME
from instore..tblSurvey sur
JOIN instore..tblSurveyStorePricingHistory hst
on hst.survey_key = sur.survey_key
JOIN instore..tblStoreGeneral stg
on stg.StoreID = hst.store_id
JOIN instore..tblDistributionCenters dc
on dc.dist_center = stg.DistCenter
where sur.survey_key = 14331
group by stg.DistCenter
,dc.dist_center + ' ' + dc.dist_center_name
order by 1
Open DC_List
While 1=1
Begin
Fetch Next from DC_List Into @DC, @DC_NAME
IF @@Fetch_Status <> 0
Break
Select * from instore..tblDistributionCenters a
where a.dist_center = @DC
END
Close DC_List
Deallocate DC_List
February 1, 2011 at 3:15 am
I am not as dogmatic on the no cursor thing... sometimes you need it. This does not seem to be one of those times!
This is quick and dirty, without data and ddl I will just give you some idea of where you can go with this...
Select * from instore..tblDistributionCenters a
where a.dist_center IN (
select
stg.DistCenter
--,dc.dist_center + ' ' + dc.dist_center_name as DC_NAME
from
instore..tblSurvey sur
JOIN instore..tblSurveyStorePricingHistory hston hst.survey_key = sur.survey_key
JOIN instore..tblStoreGeneral stgon stg.StoreID = hst.store_id
JOIN instore..tblDistributionCenters dcon dc.dist_center = stg.DistCenter
where
sur.survey_key = 14331
group by
stg.DistCenter
--,dc.dist_center + ' ' + dc.dist_center_name
order by
stg.DistCenter
)
I am sure the subquery might be further reduced to only have maybe 2 tables, I did not really have a detailed look. You might even be able to just have a query with a join in it.
February 1, 2011 at 7:30 am
Sorry - this doesn't help. I am trying to execute a block of code for each row in the table, but passing a parm that is used only in the Where clause of several subqueries in the big block of code. There is no key to join this table to the rowset returned by the big block of code, so I can't do a JOIN.
The problem seems to be that whenever I try to do a Loop, each execution of the loop creates a separate rowset. I am hoping there is some way to consolidate all the executions of the Loop into one rowset.
February 1, 2011 at 8:51 am
The code you posted creates a cursor that iterates through a normal select statement - then you execute a select statement with a where clause that is just one of the parameters of the cursor.
The stg.DistCenter (becoming the @DC variable) is used in a select statement.
I fail to see how you cannot do this with a standard select query if you are after one resultset.
Please send ddl statements, some sample data and expected output - Otherwise this is as standard as it gets.
February 1, 2011 at 9:29 am
Afraid I am not explaining the problem very well.
The select statement that I was itreating in the example was just a simple select statement that I was using as an example. It does not contain subqueries.
Here is a stripped down version of the code that I actually want to iterate:
SELECT s.survey_key
,s.description as Survey_Description
,s.survey_period_effective_date
,(select count (*)
from instore..tblSurveyStorePricingHistory h
where h.survey_key = s.survey_key
and sg.DistCenter = @DC) as Total_Stores
from instore..tblSurvey s
where s.Survey_Key in (@Survey_Key)
Let's assume the cursor returned 9 rows (or, for that matter, that I simply had a table with 9 rows), one for each DC. With a regular JOIN, the Count subquery has no way to know which DC to use.
With a loop, I can simply pass the DC variable to the count subquery each time I execute the block of code. That would give me 9 rows, once for each DC, with a count for each DC.
However, the loop does not give me one rowset. Hence my dilemma.
I'm not good with DDL. However, I might be able to construct a similar example using the Adventure database. Would that be helpful?
February 1, 2011 at 9:36 am
Yes, adventureworks will be helpful.
February 1, 2011 at 10:40 am
Solved the problem.
I created a temporary table and inserted into it each time I iterated thru the loop. Then I simply selected from the temp table at the end of the SQL statement and got one rowset with a row for each DC.
Thanks for taking the time to review my original code!
February 1, 2011 at 8:26 pm
I am a bit of a loss as to why you would do this as a cursor. How about something like ...
Select * from instore..tblDistributionCenters a
where a.dist_center in (
select stg.DistCenter
from instore..tblSurvey sur
JOIN instore..tblSurveyStorePricingHistory hst
on hst.survey_key = sur.survey_key
JOIN instore..tblStoreGeneral stg
on stg.StoreID = hst.store_id
JOIN instore..tblDistributionCenters dc
on dc.dist_center = stg.DistCenter
where sur.survey_key = 14331
group by stg.DistCenter
,dc.dist_center + ' ' + dc.dist_center_name
)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply