February 22, 2012 at 3:57 am
hi guys, just getting back into SQL and a bit ring rusty...
I have inherited a sp that does the following.
I have a table with a movement type and a number of cost columns in it
I have another table with a list movement type / cost combinations
the script currently does the following
OPEN C1
FETCH NEXT FROM C1
into @movtype,@fm
WHILE @@FETCH_STATUS=0
BEGIN
set @Counter=@Counter+1
set @sql=
'Declare ' +
'@MovType varchar(8), ' +
'@FM varchar(50) ' +
'insert into #FM_1 ' +
'select movement_type,'+
''''+@fm+''''+','+ 'sum('+@fm+'),from fsi_fm ' +
'where movement_type=' + ''''+@movtype+''''+' '+
'and '+ @fm+'<>0 '+' '+
'group by movement_type'
exec (@sql)
FETCH NEXT FROM C1
into @movtype,@fm
END
CLOSE C1
DEALLOCATE C1
#FM_1 is previously defined to take the three columns
my gut is that I should be able to do this without using cursors, maybe using UNPIVOT?
any thoughts?
February 22, 2012 at 5:42 am
it would help if we had sample tables, data and what you have tried so far along with what you want the expected result set to be.
if you could provide create table, insert scripts etc as detailed in the first link in my signature, we will be able to help you out
February 22, 2012 at 6:51 am
declare @fm int, @movtype varchar(100)
select fm
, movtype
, 0 processed
into #temp
from whereEverThisIsStored
while exists ( select top 1 * from #temp where processed = 0)
begin
select top 1 @fm = fm
, @movtype = movtype
from #temp where processed = 0
insert into #fm_1
select movement_type
,@fm
,sum(@fm)
from fsi_fm
where movement_type = @movtype
and @fm <>0
group by movement_type
update #temp
set processed = 1
where processed = 0
and @fm = fm
and @movtype = movtype
end
February 22, 2012 at 6:56 am
the above is a quick and dirty solution w/ pseudo code because you're not really providing the proper preface to give a detailed solution however it should suffice. Pretty straight forward for the most part. Load set to step through into #table w/ proceddedFlag default to zero. Loop through each record setting local variables equal to 1 records columns who processed flag = 0. Do the insert. Set that records processed flag to 1 then peel off the next record whose processed flag = 0 and so on.
February 22, 2012 at 7:11 am
captcooldaddy (2/22/2012)
the above is a quick and dirty solution w/ pseudo code because you're not really providing the proper preface to give a detailed solution however it should suffice. Pretty straight forward for the most part. Load set to step through into #table w/ proceddedFlag default to zero. Loop through each record setting local variables equal to 1 records columns who processed flag = 0. Do the insert. Set that records processed flag to 1 then peel off the next record whose processed flag = 0 and so on.
I'm pretty sure that the OP was hoping for a set-based solution. You seem to have rewritten the CURSOR with a WHILE loop - not a great leap in terms of how the solution will actually run, since we're still looking at RBAR updates/inserts. It might be possible via an insert from a CTE - but without knowing a bit more (eg, Cursor definition) I'm not certain.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
February 22, 2012 at 7:27 am
He didnt ask for set based solution but a cursor alternative. Who knows maybe your right. My solution was pre cte days. Old habits die hard.....
February 22, 2012 at 7:28 am
i see. i didnt read the bottom of his post. my bad......carry on
February 22, 2012 at 9:35 am
captcooldaddy (2/22/2012)
He didnt ask for set based solution but a cursor alternative. Who knows maybe your right. My solution was pre cte days. Old habits die hard.....
Even if the OP had asked for a cursor like alternative, cursors (and while loops) aren't necessarily the best way to accomplish most tasks. Set based alternatives are usually better for performance and scalability and should be presented as an alternative. And to be sure, set based doesn't always mean in one step, sometimes you need to break a problem down to multiple steps.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply