ToMailBox 		StartCheck		EndCheck
(Varchar (7))		(int)			(int)
--------------		------------		------------
01390			205901		        205950

chkprtbox 		chknumber 
(varchar(5))		(varchar(7))
--------- 		--------- 
01390     		0205906
01390     		0205924
01390     		0205926

declare @ID as int, declare @BegChk as int, @EndChk as int, @NoChks as int, @MBox as varchar(5), @CI as int, @ChkNo  as int,
@Out as varchar(7),  @No as varchar(7), @lNo as int, @Dif as int

set @ID     = 0
set @begchk = 0
set @endchk = 0
set @NoChks = 0
set @mbox   = null
set @CI     = 0
set @chkNo  = 0
set @Out    = Null
set @No     = Null
set @lNo    = 0 
set @dif    = 0

drop table tmpchkrng

create table tmpChkRng (ChkNo varchar(7), CI varchar(1))

--while (select top 1 chkrangeid from chkrange where chkrangeid > @ID) > 0
--begin
  set @id = (select top 1 chkrangeid from chkrange where tomailbox = '01390') --where chkrangeid > @ID)    
  set @begchk = (select startcheck from chkrange where chkrangeid = @ID)
  set @endchk = (select endcheck from chkrange where chkrangeid = @ID)
  set @nochks = (@endchk - @begchk) + 1
  set @mbox = (select tomailbox from chkrange where chkrangeid = @ID)
  set @CI = (select count(chknumber) 
              from chkreg 
              where (chkprtbox = '01390' and 
                     chknumber is not null and 
                     chktrncode = 'ci')) --@mbox)
  if @CI < @nochks 
    begin
      set @chkno = @begchk 
      while @chkno < = @endchk
        begin
          set @No = ltrim(rtrim(cast(@chkno as varchar(7))))
          set @lno = len(@no)
            if @lno <> 7 
              begin
                set @Dif =  7 - @lno
                  if @Dif = 1 set @No = '0' + @No
                  if @dif = 2 set @No = '00' + @No
                  if @Dif = 3 set @No = '000' + @No
              end                    
          set @Out   = (select chknumber from chkreg where chknumber = @No and chkprtbox = '01390')          
          if @Out is null or ltrim(rtrim(@out)) = ''
            begin
              --print 'chkno ' + cast(@chkno as varchar(7)) + ' is missing'              
              insert tmpChkRng values( @chkno, 'N')
            end  
         set @chkno = @chkno + 1      
        end
    end
--end

CREATE TABLE myTable1 (ToMailBox varchar(7), StartCheck int, EndCheck int) 
GO
CREATE TABLE myTable2 (ChkPrtBox varchar(5), ChkNumber varchar(7)) 
GO

INSERT INTO myTable1 (ToMailBox, StartCheck, EndCheck) SELECT '01390',205901,205950
GO
INSERT INTO myTable2 (ChkPrtBox, ChkNumber)
SELECT '01390','0205906' UNION ALL
SELECT '01390','0205924' UNION ALL
SELECT '01390','0205926'
GO

CREATE PROC myPROC @ToMailBox varchar(7) AS

SET NOCOUNT ON

DECLARE @StartCheck int, @EndCheck As int
DECLARE @mytable3 table (ChkNumber varchar(7) NOT NULL)

SELECT @StartCheck = StartCheck, @EndCheck = EndCheck FROM myTable1

WHILE @StartCheck <= @EndCheck
	BEGIN
		INSERT INTO @myTable3 (ChkNumber)  SELECT RIGHT(REPLICATE('0',7)+Convert(varchar(7),@StartCheck),7)
		SELECT @StartCheck = @StartCheck + 1
	END

SELECT * FROM  @myTable3 o WHERE NOT EXISTS (SELECT 1 FROM myTable2 i WHERE i.ChkNumber = o.ChkNumber)

SET NOCOUNT OFF

GO

EXEC myPROC '01390'
GO

DROP TABLE myTable1
GO
DROP TABLE myTable2
GO
DROP PROC myPROC
GO

quote:

---

Give the temp table solution a try. I bet it will work just fine for ya.

---

quote:

---

I have this issue with another report that I've created. I'm just not sure what the answer is. I need the recordset returned directly to the report instead of a temp table.

---

quote:

---

If you're using SQL2000, I would recommend using Table Datatypes as I have in my post (MOO). If you noticed there are no temp tables in it.

---

I'm sorry... I should have posted that I'm using SQL 7.

Thanks!
Teresa

quote:

---

I would think you're confusing a "Work" Table (an actual physically defined object) with a temp table.

---

You're right! I am!

Teresa

quote:

---

I'm a true believer in number tables

---

quote:

---

But don't you still have the datatype conversion problem?

---

quote:

---

chkprtbox chknumber
(varchar(5)) (varchar(7))
--------- ---------
01390 0205906
01390 0205924
01390 0205926

---

quote:

---

Looks like her range could be in the millions though

---

SELECT CAST(StartCheck AS INT) + Number - 1 as UnUsedChecks
FROM myTable1 CROSS JOIN Numbers
WHERE Number <= CAST(EndCheck AS INT) - CAST(StartCheck as INT) + 1
AND NOT EXISTS
(SELECT 1 FROM myTable2
WHERE ChkNumber = CAST(StartCheck AS INT) + Number - 1)

SELECT ((tbl1.StartCheck +Number) - 1) as ChkNo, 
       tomailbox as mBox, 
       tbl1.startcheck as Start#, 
       tbl1.endcheck as End#,           
       frmmailbox as BnkMailbox
  FROM tbl1 
  CROSS JOIN Numbers
  WHERE Number <= CAST(((tbl1.EndCheck  - tbl1.StartCheck )+1)as varchar(7))
        AND NOT EXISTS  (SELECT 1 
                         FROM tbl2
                         WHERE Chknumber = replicate ('0', 7 - len(ltrim(rtrim(cast(tbl1.startcheck +number - 1 as varchar))))) 
                                           +ltrim(rtrim(cast(tbl1.startcheck +number - 1 as varchar)))
                         )

quote:

---

I agreed with Rob, 'cause he knows a lot about SQL , until I worked with this today.

---

HA! That'll be the last time you ever agree with me!

I should've clarified that you didn't need a TEMP table either...just any regular, indexable table would work, especially a numbers table like everyone suggested.

And I'm also a HUUUUUUUUUUUGE believer in numbers/sequence/tally tables:

http://www.sqlteam.com/item.asp?ItemID=2652