Insert query and Cursor in same stored procedure
-
hi, i think you need to create a transaction for insert and then commit, there after you retrieve and update. reagrds
modified on Thursday, January 22, 2009 5:49 AM
Yes, I thought this also and did try it but it didn't seem to work. Maybe I coded it incorrectly. I will try again! Thanks for your help :)
-
Thanks for your time. I understand what you're saying about cursors being a performance hog, but there are going to be at most 30 records in this table so I didn't think using a cursor would have too much of an impact. Am I wrong in thinkign this? Anyway, here is my procedure:
ALTER PROCEDURE [dbo].[spReportTabINSERT]
-- Add the parameters for the stored procedure here
@prmName as varchar(30),
@prmSequence as int
AS
BEGIN
SET NOCOUNT ON;INSERT INTO tblReportTabs ( \[name\], \[Sequence\] ) VALUES ( @prmName, @prmSequence ) -- update all sequences DECLARE @ID uniqueidentifier DECLARE @Sequence int DECLARE @NewSequence int SET @NewSequence = 10 DECLARE crsTabs CURSOR FOR SELECT tblReportTabs.\[ID\], tblReportTabs.\[Sequence\] FROM tblReportTabs ORDER BY tblReportTabs.\[Sequence\] OPEN crsTabs FETCH NEXT FROM crsTabs INTO @ID, @Sequence WHILE @@FETCH\_STATUS = 0 BEGIN UPDATE tblReportTabs SET tblReportTabs.\[Sequence\] = @NewSequence WHERE tblReportTabs.\[ID\] = @ID SET @NewSequence = @NewSequence + 10 FETCH NEXT FROM crsTabs ENDEND
CLOSE crsTabs
DEALLOCATE crsTabs -
The first observation is that in the end of the loop, you should have:
FETCH NEXT FROM crsTabs INTO @ID, @Sequence
instead of
FETCH NEXT FROM crsTabs
The need to optimize rises from a bad design.My articles[^]
Great, fixed!! School boy error aye?! Thanks a lot :)
-
Great, fixed!! School boy error aye?! Thanks a lot :)
No problem.
Liqz wrote:
School boy error aye
It happens to everyone :) Another thing, I didn't quite get the logic in your loop. You fetched the sequence but you didn't use it anywhere, was that intentional?
The need to optimize rises from a bad design.My articles[^]
-
No problem.
Liqz wrote:
School boy error aye
It happens to everyone :) Another thing, I didn't quite get the logic in your loop. You fetched the sequence but you didn't use it anywhere, was that intentional?
The need to optimize rises from a bad design.My articles[^]
Again, you're right. I am updating the sequence with @NewSequence, but of course, I do not need to fetch it in order to do this. I will amend it. Thanks again :)
-
Again, you're right. I am updating the sequence with @NewSequence, but of course, I do not need to fetch it in order to do this. I will amend it. Thanks again :)
-
Thanks for your time. I understand what you're saying about cursors being a performance hog, but there are going to be at most 30 records in this table so I didn't think using a cursor would have too much of an impact. Am I wrong in thinkign this? Anyway, here is my procedure:
ALTER PROCEDURE [dbo].[spReportTabINSERT]
-- Add the parameters for the stored procedure here
@prmName as varchar(30),
@prmSequence as int
AS
BEGIN
SET NOCOUNT ON;INSERT INTO tblReportTabs ( \[name\], \[Sequence\] ) VALUES ( @prmName, @prmSequence ) -- update all sequences DECLARE @ID uniqueidentifier DECLARE @Sequence int DECLARE @NewSequence int SET @NewSequence = 10 DECLARE crsTabs CURSOR FOR SELECT tblReportTabs.\[ID\], tblReportTabs.\[Sequence\] FROM tblReportTabs ORDER BY tblReportTabs.\[Sequence\] OPEN crsTabs FETCH NEXT FROM crsTabs INTO @ID, @Sequence WHILE @@FETCH\_STATUS = 0 BEGIN UPDATE tblReportTabs SET tblReportTabs.\[Sequence\] = @NewSequence WHERE tblReportTabs.\[ID\] = @ID SET @NewSequence = @NewSequence + 10 FETCH NEXT FROM crsTabs ENDEND
CLOSE crsTabs
DEALLOCATE crsTabsI must be off my rocker but I can't see why you are using a cursor for this logic.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
If you don't ask questions the answers won't stand in your way.
Most of this sig is for Google, not ego. -
I must be off my rocker but I can't see why you are using a cursor for this logic.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
If you don't ask questions the answers won't stand in your way.
Most of this sig is for Google, not ego.Because I need to loop through each record in a table and update a field based on an incrementing integer. I'm failry new to all this so if you have any other ideas then I'm all ears!
-
Because I need to loop through each record in a table and update a field based on an incrementing integer. I'm failry new to all this so if you have any other ideas then I'm all ears!
Well autonumbers are the way to do incrementing integers in Sql Server and sequences in Oracle (look-up either SCOPE_IDENTITY() or sequence NextVal depending on your poison of choice) However, to get your code to work it seems to me: 1) Begin Transaction 2) Insert 3) UPDATE set sequence = sequence + 10 WHERE sequence > @sequence 4) UPDATE set sequence = sequence + 10 where id = @id 5) COMMIT CURSORS are evil and should be avoided.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
If you don't ask questions the answers won't stand in your way.
Most of this sig is for Google, not ego. -
Well autonumbers are the way to do incrementing integers in Sql Server and sequences in Oracle (look-up either SCOPE_IDENTITY() or sequence NextVal depending on your poison of choice) However, to get your code to work it seems to me: 1) Begin Transaction 2) Insert 3) UPDATE set sequence = sequence + 10 WHERE sequence > @sequence 4) UPDATE set sequence = sequence + 10 where id = @id 5) COMMIT CURSORS are evil and should be avoided.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
If you don't ask questions the answers won't stand in your way.
Most of this sig is for Google, not ego.Thanks for your response. I understand your concern about cursors but unfortunately I don't think your idea is going to work. The reason is because the insert statement is inserting a record with a sequence which isn't a multiple of 10, i.e. 15, 25, 35 etc. This then means that the newly inserted record is in the right place accoring to sequence number. The cursor then gets all the records ordered by sequence, and updates them back to numbers divisible by 10, including the newly inserted record (obviously, the sequence doesn't have to be multiples of 10, it could of easily have been 2, but still). Again, I realise that cursors are not a good idea but I don't see any other way around it (other than doing it from my VB.NET application with multiple database calls which definately isn't a good idea). Also, there are going to be at most 30 records in this table so I thought that using a cursor wouldn't be too much of a problem. Like I said, I am fairly new to this so any other suggestions you may have are welcomed.
-
Thanks for your response. I understand your concern about cursors but unfortunately I don't think your idea is going to work. The reason is because the insert statement is inserting a record with a sequence which isn't a multiple of 10, i.e. 15, 25, 35 etc. This then means that the newly inserted record is in the right place accoring to sequence number. The cursor then gets all the records ordered by sequence, and updates them back to numbers divisible by 10, including the newly inserted record (obviously, the sequence doesn't have to be multiples of 10, it could of easily have been 2, but still). Again, I realise that cursors are not a good idea but I don't see any other way around it (other than doing it from my VB.NET application with multiple database calls which definately isn't a good idea). Also, there are going to be at most 30 records in this table so I thought that using a cursor wouldn't be too much of a problem. Like I said, I am fairly new to this so any other suggestions you may have are welcomed.
I highly recommend you reevaluate your logic in your application and database. I think you will find the answer should come to you. While I do not know what you are doing, I have a pretty good idea.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
If you don't ask questions the answers won't stand in your way.
Most of this sig is for Google, not ego. -
Thanks for your response. I understand your concern about cursors but unfortunately I don't think your idea is going to work. The reason is because the insert statement is inserting a record with a sequence which isn't a multiple of 10, i.e. 15, 25, 35 etc. This then means that the newly inserted record is in the right place accoring to sequence number. The cursor then gets all the records ordered by sequence, and updates them back to numbers divisible by 10, including the newly inserted record (obviously, the sequence doesn't have to be multiples of 10, it could of easily have been 2, but still). Again, I realise that cursors are not a good idea but I don't see any other way around it (other than doing it from my VB.NET application with multiple database calls which definately isn't a good idea). Also, there are going to be at most 30 records in this table so I thought that using a cursor wouldn't be too much of a problem. Like I said, I am fairly new to this so any other suggestions you may have are welcomed.
I understood that you're trying to do ordering. For example you have ordinals 1,2,3,4 and 5 then you add a new record which has the ordinal 3 so old ordinals 3,4 and 5 will be replaced with 4,5 and 6 respectively. If that's the case, you could simply: - update all rows, set ordinal = ordinal + 1 where ordinal >= 3 - insert the new row with ordinal 3
The need to optimize rises from a bad design.My articles[^]
-
I highly recommend you reevaluate your logic in your application and database. I think you will find the answer should come to you. While I do not know what you are doing, I have a pretty good idea.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
If you don't ask questions the answers won't stand in your way.
Most of this sig is for Google, not ego.Ok Thanks. I have just looked at your example again and now understand a little more where you're coming from and i think that it would acually work for my insert statement by changing one of the lines from
UPDATE set sequence = sequence + 10 where id = @id
to
UPDATE set sequence = sequence + 5 where id = @id
which is great and I will use this, thanks :) But I have a similar stored procedure for doing an update on an existing record, that is, updating the sequence of one of the records already in the table. Maybe I will look into my logic further for this too. Thanks again :)
-
I understood that you're trying to do ordering. For example you have ordinals 1,2,3,4 and 5 then you add a new record which has the ordinal 3 so old ordinals 3,4 and 5 will be replaced with 4,5 and 6 respectively. If that's the case, you could simply: - update all rows, set ordinal = ordinal + 1 where ordinal >= 3 - insert the new row with ordinal 3
The need to optimize rises from a bad design.My articles[^]
That is actually a good and very simple idea too, why didn't I think of that! Will try that when I get to work tomorrow! Thanks :)
-
That is actually a good and very simple idea too, why didn't I think of that! Will try that when I get to work tomorrow! Thanks :)
-
I understood that you're trying to do ordering. For example you have ordinals 1,2,3,4 and 5 then you add a new record which has the ordinal 3 so old ordinals 3,4 and 5 will be replaced with 4,5 and 6 respectively. If that's the case, you could simply: - update all rows, set ordinal = ordinal + 1 where ordinal >= 3 - insert the new row with ordinal 3
The need to optimize rises from a bad design.My articles[^]
This works great when inserting a new record and then updating each sequence but not when changing an existing record's sequence and then updating all the other sequences. But, with my new found knowledge, I'm sure that I'll work it out! Thanks again all for your help.
-
This works great when inserting a new record and then updating each sequence but not when changing an existing record's sequence and then updating all the other sequences. But, with my new found knowledge, I'm sure that I'll work it out! Thanks again all for your help.