Mysterious INSERT Skip - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

Mysterious INSERT Skip

Author

Topic

dzieba
Starting Member

3 Posts

Posted - 2011-06-11 : 10:33:57

Hi,

I have an unusual problem that does not have a rhyme or reason to it... in my mind so I am hoping for some help.

This insert statement is inside a look at works just fine for every item in the loop.

INSERT INTO #SplitBillingItemsPriceChange2
VALUES (666, @rowNum, @mappedRateScheduleId,
dbo.RateScheduleSeason(@mappedRateScheduleId, dateadd(day, 1, @itemFromDate)),
@splitFraction, @meterId, @itemFromDate, @itemToDate,
@days, @rateScheduleOnBill, @electricCharges, @electricUsage, @billingLoadDemand,
@maximumDemand, @summerMaxDemand, @winterMaxDemand, @onDemand, @partDemand, @summerPartDemand, @winterPartDemand,
@offDemand, @onkWh, @partkWh, @offkWh, @monthlyBillingFactor)

However, once I put this statement in front of the insert statement... the insert does not happen on some of the records in the loop. It just mysteriously gets ignored. Other records do get inserted. I'm loosing sleep and hair over this.

SELECT TOP 1 @mappedRateScheduleId = id FROM BillingRateSchedule
WHERE (billingRateId = @billingRateId) AND (effectiveDate <= @itemFromDate)

I hope someone here has come accross this and can help me.

Thank you,

Table definition below incase it's needed.

CREATE TABLE #SplitBillingItemsPriceChange2
(
[scheduleCount] [int] NOT NULL,
[rowId] [int] NOT NULL,
[mappedRateScheduleId] [uniqueidentifier] NULL,
[seasonName] [varchar](50) NULL,
[splitFraction] [float] NULL,
[meterId] [uniqueidentifier] NOT NULL,
[fromDate] [date] NOT NULL,
[toDate] [date] NOT NULL,
[days] [int] NOT NULL,
[rateScheduleOnBill] [varchar](50) NOT NULL,
[electricCharges] [money] NOT NULL,
[electricUsage] [float] NOT NULL,
[billingLoadDemand] [int] NOT NULL,
[maximumDemand] [float] NOT NULL,
[summerMaxDemand] [int] NULL,
[winterMaxDemand] [int] NULL,
[onDemand] [float] NULL,
[partDemand] [float] NULL,
[summerPartDemand] [int] NULL,
[winterPartDemand] [int] NULL,
[offDemand] [float] NULL,
[onkWh] [float] NULL,
[partkWh] [float] NULL,
[offkWh] [float] NULL,
[mbf] [float] NULL
)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-06-11 : 10:48:18

Maybe the variable doesn't get set?
Is it the same rows missed every time?

Put a try catch block around it to see if it traps an error.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

dzieba
Starting Member

3 Posts

Posted - 2011-06-11 : 12:50:23

quote:
Originally posted by nigelrivett

Maybe the variable doesn't get set?
Is it the same rows missed every time?

Put a try catch block around it to see if it traps an error.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

The variable does get set and even if did not it should still work because the table allows NULLS.

This process is the first loop which puts data into one table. The same stored proc also does another look and puts rows in a different table. I removed all code from the second loop so it only moves rows from one temp table to the other.
Once I removed the second loop it works fine.
It's like it wants me to process data in only one temp table. Still not sure what the problem actually is but it seems to work if I stick to using only one table.

How does a TRY-CATCH block look in SQL? I will try it.

thanks for your help.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-11 : 13:44:41

There are a few examples on this page: http://msdn.microsoft.com/en-us/library/ms175976.aspx

In your case, it would be something like this with all of this within your loop:


begin try
   INSERT INTO #SplitBillingItemsPriceChange2 
   VALUES (666, @rowNum, @mappedRateScheduleId, 
   dbo.RateScheduleSeason(@mappedRateScheduleId, dateadd(day, 1, @itemFromDate)), 
   @splitFraction, @meterId, @itemFromDate, @itemToDate, 
   @days, @rateScheduleOnBill, @electricCharges, @electricUsage, @billingLoadDemand,
   @maximumDemand, @summerMaxDemand, @winterMaxDemand, @onDemand, @partDemand, @summerPartDemand, @winterPartDemand,
   @offDemand, @onkWh, @partkWh, @offkWh, @monthlyBillingFactor)
   
   if (@@rowcount = 0) raiserror('Aha! caught you!! %d',16,1,@rowNum);
end try
begin catch
   SELECT ERROR_MESSAGE() AS ErrorMessage
end catch

dzieba
Starting Member

3 Posts

Posted - 2011-06-11 : 14:33:47

Thank you! I will definetly use your code. Right now I am reconstructing to only use one table for the three loops I need to make through a changing number of records. It's fun.

Thanks again for your time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources