Need to Repeat an Insert Once if Condition is Met Inside a Cursor

[dburrows1278]

I'm creating a fixed width report for a client and on occasion there are quantities that break the width rule in their requirement. In this instance, I need the quantities split in half and inserted in twice.

The report looks like this, all done with a cursor:
-----header line-----
---dtl line item-----
---dtl line item-----
---dtl line item-----
---dtl line item-----
----end--------------

I just don't know how to say if the length of quantity is greater than 4, repeat this line twice while doing the insert. Any ideas?

 

[SQLSister]

OK I would never consider creating a report using a cursor. Isn't there a set-based way to get the data? Cursors are evil and should be avoided.

But given this very strange requirement, I would probaly put everything into a temp table as I worked and once every record was there, I would do another insert that was something like:

Insert #temp (field1, field2)
select field1, substring(field2, 5, len(field2)
from #temp where len(field2)>5

That would get you the extra records. Then I would update the temp table to get rid of the extra characters inteh orginal insert.

update #temp
set field2 = left (field2, 5)
where len(field2) >5

Then select everything from the #temp table for the report. You'll probably need something to order on to ensure the two records appear together, but you can include that in your #temp table definiton if need be.

"NOTHING is more important in a database than integrity." ESquared