SQLTeam.com | Weblogs | Forums

Error in my code



I have created the following SQL statement

set @cnt = 0
WHILE @cnt <= (select count(prodref) from dba.proditem where prodref like '840%')
INSERT INTO dba.proditemlinks
(opco_code,prodref,linked_prodref, defaultquantity, ratioquantity, linktype, triggercascade, removeparentitem, startdate, enddate,sequence, invisible, serviceitem, cataloguecode)
select opco_code,(Select top 1 t1.prodref from dba.proditem as t1 LEFT JOIN dba.proditemlinks as t2 ON t1.prodref = t2.prodref where t1.prodref like '840%' and t2.prodref is null),linked_prodref, defaultquantity, ratioquantity, linktype, triggercascade, removeparentitem, startdate, enddate,sequence, invisible, serviceitem, cataloguecode from dba.proditemlinks where prodref = '840' 
set @cnt = @cnt+1

Which appears to work well (it may not be the best option, but with my limited know how and a little help from these forums its working) until I think it gets to the end and gives me an error that dba.proditemlinks.prodref cannot be null.

Can anyone help me see the error that I am clearly missing!


The column prodref in dba.proditemlinks table may be set to not allow NULL.
Check the column property.


That will re-Count every time around the loop, not sure that is the best way. If you just need to know the number of rows then I would do the COUNT first (into a @TempVariable) and use that in your WHILE loop.

If what you actually want? is to process each matched row inside your loop (which you aren't currently doing, I presume your loop is reselecting the same row each tmie) then I would do the following:

Get the PKey for all matching rows into a #TEMP table together with an IDENTITY value (SORT the values, IF you want them processed in a specific order inside your LOOP)

      PKey1, PKey2, ...
INTO #TempTableName
from dba.proditem 
where prodref like '840%'
-- optional ORDER BY ...
SELECT @TotalCnt = @@ROWCOUNT -- Remember the number of rows added to #TempTableName

then for your loop

set @LoopRow = 1
WHILE @LoopRow <=  @TotalCnt
SELECT @LoopRow = @LoopRow + 1

Inside the loop, you can get the actual row from [proditem] using a JOIN:

FROM #TempTableName AS T
     JOIN dba.proditemlinks AS PIL
         ON PIL.PKey1 = T.PKey1
        AND PIL.PKey2 = T.PKey2
WHERE T.T_ID = @LoopRow -- Only matches the "current" row for the LOOP

But :slight_smile: if all you want to do is to INSERT some new rows into [proditemlinks] for every occurrence

FROM dbo.proditem
WHERE prodref like '840%'

then you ought to be able to do something like:

INSERT INTO dba.proditemlinks
SELECT opco_code, RelevantColumn(s)From[proditem], ...
FROM dba.proditem
WHERE prodref like '840%'


Thanks, the dba.proditemlinks doesn't accept a null for prodref.

I am trying to find out where the null value is coming from.


Hi Kristen, thanks again for the help.

We create new products throughout the day into dba.proditem some of the items created need to be added to the dba.proditemlinks table, and I have to create 43 lines against each new product that is created into this table. The idea is in our system if you buy 'X' you might also want Y,Z,etc.

So I created the insert statement which copies the 43 lines from an existing product in dba.proditemlinks and creates them with the new part number.

So to automate this I created the Loop. My idea was I would count how many new products there were, the loop through them, only selecting the top 1 item each time, and run the insert command.

Hopefully that makes sense.

It seems to work, it just errors at the end.


It would be better to do that in a single INSERT statement (that inserted all 43 rows as one batch).

If you can SELECT the relevant 43 rows (from Product table, maybe?), then that would form the basis of the INSERT statement.

Isn't your "if you buy 'X' you might also want Y,Z,etc." based on sales? i.e. for a new product that will be no suitable data?

Personally I would re-fresh the 43 items (I presume that is the number of "Similar / companion products" that displays on your front end?) each night, based on actual sales

In our eCommerce stuff we have "hints" and all sorts for this ...

When the product is set up the operator can choose companion products - batteries and accessories etc.

An operator can set up push-products - slow moving items with high inventory stock numbers :frowning:

If we get a TV mention :slight_smile: we may want to raise the visibility of that product (if we have plenty of stock), or (if minimal stock) we avoid showing it on any companion list (unless it is explicitly marked as a companion product), so that we will retain stock for people who specifically look for that product, but not attempt to sell it as an up-sale.


Hi Kristen,

Our related products required for the item being sold. The reason there is so many options is different lengths; 1m, 2m etc.

If I am right my insert statement is acting like a 'copy' button. Is this what you are saying is no good?


Its the Loop that is "no good", it would be better to do a single insert, that inserts 43 rows, that loop 43 times inserting one row each loop.

SQL is much (as in much MUCH!!) better at doing Set-Based work than RBAR (Row-By-Agonising-Row)

Sorry if I'm answering the wrong question.