Multiple INSERT statements in a BEGIN...END block

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Multiple INSERT statements in a BEGIN...END block

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-20 : 09:11:46

John writes "The following stored procedure gets stuck in a continuous loop because the FETCH_STATUS is always zero. If I comment out the second INSERT statement, the cursor runs fine. Is there some restriction or special syntax required when using multiple INSERT statements? I have used multiple UPDATE statements inside a cursor with no problem.

CREATE procedure Drew_Temp_SetUpBloomberRatesForOperation

as

declare @BaseRateID int
declare @RateSourceLID int
declare @RateAvailabilityFrequencyLID int
declare @RateUpdateFrequencyLID int
declare @RateAutoUpdateFlag bit
declare @RateSymbol varchar(20)
declare @RateSeriesInceptionDate smalldatetime
declare @RateFromCurrencyLID int
declare @RateCurrencyToLID int
declare @DayOffset int
declare @NewRateID int
declare @ErrorDescription varchar(400)

declare bb_rates cursor for select cbr.BaseRateID,RateSourceLID, RateAvailabilityFrequencyLID, RateUpdateFrequencyLID,
RateAutoUpdateFlag,RateSymbol,RateSeriesInceptionDate,RateFromCurrencyLID,RateCurrencyToLID,DayOffset from common_baserate cbr
inner join common_ratemap crm on cbr.baserateid = crm.baserateid inner join common_rate cr on crm.rateid = cr.rateid

open bb_rates

fetch next from bb_rates into @BaseRateID,@RateSourceLID, @RateAvailabilityFrequencyLID, @RateUpdateFrequencyLID,
@RateAutoUpdateFlag, @RateSymbol, @RateSeriesInceptionDate, @RateFromCurrencyLID, @RateCurrencyToLID, @DayOffset

While @@fetch_status <> -1
BEGIN
Select @RateSourceLID = 1183

--insert into the Common_Rate table
INSERT INTO Common_Rate([RateSourceLID], [RateAvailabilityFrequencyLID], [RateUpdateFrequencyLID], [RateAutoUpdateFlag], [RateSymbol], [RateSeriesInceptionDate], [RateFromCurrencyLID], [RateCurrencyToLID], [DayOffset])
VALUES(@RateSourceLID,@RateAvailabilityFrequencyLID,@RateUpdateFrequencyLID,@RateAutoUpdateFlag,@RateSymbol,@RateSeriesInceptionDate,@RateFromCurrencyLID,@RateCurrencyToLID,@DayOffset)

Select @NewRateID = max(RateID) from Common_Rate

--insert into the Common_RateMap table
Insert Into Common_RateMap VALUES (@BaseRateID, @NewRateID)

fetch next from bb_rates into @BaseRateID,@RateSourceLID, @RateAvailabilityFrequencyLID, @RateUpdateFrequencyLID,
@RateAutoUpdateFlag, @RateSymbol, @RateSeriesInceptionDate, @RateFromCurrencyLID, @RateCurrencyToLID, @DayOffset

END

close bb_rates
deallocate bb_rates
GO"

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-20 : 12:36:23

Why not just use the SELECT in the cursor declaration in the INSERT with a JOIN? That would be used instead of a cursor.

INSERT INTO Common_Rate (([RateSourceLID], [RateAvailabilityFrequencyLID], [RateUpdateFrequencyLID], [RateAutoUpdateFlag], [RateSymbol], [RateSeriesInceptionDate], [RateFromCurrencyLID], [RateCurrencyToLID], [DayOffset])
SELECT RateSourceLID, RateAvailabilityFrequencyLID, RateUpdateFrequencyLID, RateAutoUpdateFlag,RateSymbol,RateSeriesInceptionDate,RateFromCurrencyLID,RateCurrencyToLID,DayOffset
FROM common_baserate cbr
inner join common_ratemap crm
on cbr.baserateid = crm.baserateid
inner join common_rate cr
on crm.rateid = cr.rateid

Tara

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-05-20 : 12:57:26

Updateing Common_RateMap (a table joined in the cursor declaration) causes the results returned in the cursor to be inconsistent.

Change to

declare bb_rates INSENSITIVE cursor for...

or add an ORDER BY to the cursor select statement. (Both methods cause a temporary table to be created which the cursor selects from)

(A couple of set based methods may be possible Tara, but John updates Common_Rate and Common_RateMap)

Ken

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources