dd_IV00101_SEL_inRangesOf100

If you're a SQL weenie, please read this all the way through. <smiles> You'll get something out of it.

I'm being asked to send a file to a vendor via a web service, but the vendor can't handle the entire file <sigh> (If they'd hire me, they could <laughs>)

So, I have to break the file down into chunks of 100. This happens to be items, but the technique applies to any large file. It needs to be dynamic because the client's inventory constantly changes and I can't just specify ranges of items, the ranges change.

So, this code brings back a range of exactly 100 items and I use this to send the vendor x files of 100 items. Very cool technique. Love to hear your comments!

This is the output of the code below.

Related Articles

... and you 'll find more on the SQL (Dynamics) Menu

IF EXISTS (SELECT name
    FROM   sysobjects
    WHERE  name = N'dd_IV00101_SEL_inRangesOf100'
        AND type = 'P')
    DROP PROCEDURE dd_IV00101_SEL_inRangesOf100
GO
 
CREATE PROCEDURE dd_IV00101_SEL_inRangesOf100
 
AS
 
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
 
--get the row count of the IV00100 table, so we can get the last row
declare @rowcount int
select @rowcount = sum(1)
    from iv00101
 
 
select  ITEMNMBR,
        RowID
    into #tmp
    from (
        --this query returns all the ITEMNMBRS and an artificial RowID
        --we have to use a subquery because we can't put ROW_NUMBER in the WHERE clause
        SELECT
                ITEMNMBR,
                ROW_NUMBER() OVER (ORDER BY ITEMNMBR) AS ROWID
            FROM IV00101 i
    ) i
    --Use the MOD function (%) to get every 100th row, and the first and last row
    where convert(numeric(19,1),rowid) % 100 =  0 or Rowid = 1 or ROWID = @rowcount
    order by rowid
 
select
        --use the LAG function to get the previous ITEMNMBR
        isnull(lag(itemnmbr) over (order by RowID),'') as StartItem,
        ITEMNMBR as EndItem,
        RowID
    from #tmp
    --we don't want the first row, because there is no previous row
    where not rowid = 1
 
 
 
GO
grant exec on dd_IV00101_SEL_inRangesOf100 to public
--sp_sps

RealWorldCode gives developers practical, real‑world solutions with clean, working code — no fluff, no theory, just answers.
Links
Home
Knowledge Areas
Sitemap
Contact
Et cetera
Privacy Policy
Terms and Conditions
Cookie Preferences