T-SQL error Possibly a scope issue?

Question

I have this T-SQL code in a procedure, I'm only posting the relevant parts for brevity.

declare @tIDs TABLE (ID int)

While 1=1
    begin
    set @iCnt = @iCnt + 1
    if @iCnt > @iNumberDuplication break
    set @iLoop = 0
    declare PHcursor cursor for (select REPORT_CUBE_ID from @tPULSE_HYPERCUBE)
    open PHcursor
    While 1=1
        begin
            set @iLoop = @iLoop + 1
            if  @iLoop > @iPHrows break
            fetch next from PHcursor into @tIDs
            set @iCurrID = (select ID from @tIDs)

            set @iIDloop = @iIDloop + 1
            set @iREPORT_CUBE_ID = 90000000000000 + @iIDloop
            UPDATE @tPULSE_HYPERCUBE    SET REPORT_CUBE_ID = @iREPORT_CUBE_ID WHERE REPORT_CUBE_ID = @iCurrID
            UPDATE @tPULSE_METRIC_DETAILS   SET REPORT_CUBE_ID = @iREPORT_CUBE_ID WHERE REPORT_CUBE_ID = @iCurrID
            DELETE FROM @tIDs

        end
    CLOSE PHcursor
    DEALLOCATE PHcursor

    insert into X_PULSE_HYPERCUBE (REPORT_CUBE_ID, CM_PHY_OWNER_ID, CM_LOG_OWNER_ID,INTERVAL_C,INTERVAL_START_DATE,PULSE_METRIC_ID,USER_ID) (select * from @tPULSE_HYPERCUBE)


    end

I am getting an error that I must declare the scalar variable @tIDs I already have declared it though. Is this a scope issue? It wont go away.

My first comment is that you should NEVER use a cursor for this task. If you use a set-based update your problem will go away and it will be much faster. Cursore are a last resort, never a first resort. — HLGEM
– HLGEM, Commented Apr 24, 2014 at 16:40
What does a set based update look like? I'm sorry I'm new to T-SQL (actually this is my second day using it) — Thomas Robert Horn
– Thomas Robert Horn, Commented Apr 24, 2014 at 16:42
wiki.lessthandot.com/index.php/Cursors_and_How_to_Avoid_Them — HLGEM
– HLGEM, Commented Apr 24, 2014 at 16:44
BTW you shoudl have at least 5 years experience writing SQL before even attempting a cursor, buy then you should know better. — HLGEM
– HLGEM, Commented Apr 24, 2014 at 16:45

Martin Robins · Accepted Answer · 2014-04-24 17:13:42Z

1

Try this...

declare @loop integer = 30000;

while @loop > 0 begin

  insert into @tPULSE_HYPERCUBE (REPORT_CUBE_ID, col2, col3...)
  select REPORT_CUBE_ID + 90000000000000 + @loop, col2, col3...
  from @tPULSE_HYPERCUBE 
  where REPORT_CUBE_ID < 90000000000000;

  set @loop = @loop - 1;

end

answered Apr 24, 2014 at 17:13

Martin Robins

6,18311 gold badges61 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thomas Robert Horn Over a year ago

My final solution looks somewhat like this. Loop should increment by the maxID in the base table instead of by 1 though.

Martin Robins · Accepted Answer · 2014-04-24 16:52:24Z

1

Looking again, @HLGEM is correct; this should be a couple of simple update statements...

update m
set REPORT_CUBE_ID = REPORT_CUBE_ID + 90000000000000
from @tPULSE_HYPERCUBE h
  inner join @tPULSE_METRIC_DETAILS m on h.REPORT_CUBE_ID = m.REPORT_CUBE_ID;

update @tPULSE_HYPERCUBE 
set REPORT_CUBE_ID = REPORT_CUBE_ID + 90000000000000;

edited Apr 24, 2014 at 16:52

answered Apr 24, 2014 at 16:44

Martin Robins

6,18311 gold badges61 silver badges96 bronze badges

Comments

Martin Robins · Accepted Answer · 2014-04-24 16:53:09Z

0

Looks to me as if you are trying to use the in-memory table "@tIDs" as the recipient for the REPORT_CUBE_ID column within the cursor ("fetch next from PHCursor into @tIDs"). The recipient variable for the value of REPORT_CUBE_ID should be defined as the same data type as REPORT_CUBE_ID.

Not too sure what you are doing on the next line either; but it looks like you are trying to get back the REPORT_CUBE_ID value from the same table @tIDs?

Assuming REPORT_CUBE_ID is an integer, define @tIDs as an integer also and then when you "fetch next from PHCursor into @tIDs", @tIDs will contain the value of REPORT_CUBE_ID for the current cursor row and you will not need to then access it with @iCurrID.

Better still, "fetch next from PHCursor into @iCurrID" and get rid of @tIDs altogether.

edited Apr 24, 2014 at 16:53

answered Apr 24, 2014 at 16:41

Martin Robins

6,18311 gold badges61 silver badges96 bronze badges

3 Comments

Thomas Robert Horn Over a year ago

This works, although it sounds as if I should not be doing it this way to begin with.

Martin Robins Over a year ago

If my (and @HLGEM) are understanding your intention correctly, then no - you should not be doing it the way you are. It looks like you are simply incrementing the REPORT_CUBE_ID in two related tables (in-memory tables no less); this being the case, see my other answer which I suspect will not only be easier but also quicker.

Thomas Robert Horn Over a year ago

My exact goal is to do this: duplicate all the rows in PULSE_HYPERCUBE X times. I need to create new unique primary keys for all those rows. Your solution below will work for 1 iteration but then the next iteration REPORT_CUBE_ID + 900000000000 has already been done. That is why I had the @iIDloop variable that incremented independently. I'm doing this to get some performance data with very large data sets (30 million rows or so)

Collectives™ on Stack Overflow

T-SQL error Possibly a scope issue?

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related