Error in Inserting Value - 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)

Error in Inserting Value

Author

Topic

psfaro
Starting Member

49 Posts

Posted - 2010-10-07 : 08:02:42

Hi,
The folowing T-SQL code:

--create Temp Table with 2 more fields
select *,acumulado=0, row_number() over (order by orderId) as recno into #TEMP from [Order Details]

--vars
DECLARE @nregs INT
DECLARE @rec INT
DECLARE @tot INT
DECLARE @qt INT

--initialize
set @tot=0
set @rec=0
set @nregs= (select COUNT(*) from #TEMP)

--loop
WHILE @rec <= @nregs

BEGIN

set @qt= (select quantity from #temp
WHERE recno=@rec)

set @tot= @tot + @qt

UPDATE #TEMP SET acumulado=@tot
WHERE recno=@rec

set @rec=@rec + 1
END

select * from #TEMP

drop table #TEMP
-----------------------------
Returns:

Cannot insert the value NULL into column 'acumulado', table 'tempdb.dbo.#TEMP

NOTES:

if i change:

set @tot= @qt

works Fine, what means that @qt doesn't have any NULL value.

[Order Details] is the Table of Nortwind Database

SQLSERVER R2

Regards

Pedro

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-07 : 08:19:43

quote:
Originally posted by psfaro

Hi,
The folowing T-SQL code:

--create Temp Table with 2 more fields
select *,acumulado=0, row_number() over (order by orderId) as recno into #TEMP from [Order Details]

--vars
DECLARE @nregs INT
DECLARE @rec INT
DECLARE @tot INT
DECLARE @qt INT

--initialize
set @tot=0
set @rec=0
set @nregs= (select COUNT(*) from #TEMP)

--loop
WHILE @rec <= @nregs

BEGIN

set @qt= (select quantity from #temp
WHERE recno=@rec)

set @tot= @tot + @qt

UPDATE #TEMP SET acumulado=@tot
WHERE recno=@rec

set @rec=@rec + 1
END

select * from #TEMP

drop table #TEMP
-----------------------------
Returns:

Cannot insert the value NULL into column 'acumulado', table 'tempdb.dbo.#TEMP

NOTES:

if i change:

set @tot= @qt

works Fine, what means that @qt doesn't have any NULL value.

[Order Details] is the Table of Nortwind Database

SQLSERVER R2

Regards

Pedro

Please correct me if my understanding is wrong.

WHILE @rec <= @nregs --You are starting loop with 0 instead of one.

set @qt= (select quantity from #temp
WHERE recno=@rec) --This will return null .

set @tot= @tot + @qt -- Any concatenation will null returns null

UPDATE #TEMP SET acumulado=@tot --@tot have null so updation fails
WHERE recno=@rec

Why you are using while loop for aggregation.

psfaro
Starting Member

49 Posts

Posted - 2010-10-07 : 09:25:34

Hi pk_bohra

You absoluty Right !! Thank's

If i change :

set @rec=1 works fine.

Still not understand why even in this situation

set @rec=0

set @tot= @qt //must return NULL

doesn't return any error and "acumulado" column have the same value as Quantity Column,

This was my confusion that i forgot to initialize @rec=1

Regards

Pedro

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-07 : 13:58:55

Hi Pedro,

Happy to hear that explanation worked for you.

Regarding the query

quote:
set @tot= @qt //must return NULL

.

I have never come across such strange behavior of SQL.
At this point I don't have answer for your query.

May be some senior member knows the answer for it.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources