I decided to go with the Cursor

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 2005 Forums

Transact-SQL (2005)

I decided to go with the Cursor

Author

Topic

Trudye
Posting Yak Master

169 Posts

Posted - 2009-08-20 : 15:37:35

For some reason my cursor only reads one record, it does not loop (the TRL file contains 3 recs. Can someone tell me what I am missing?

DECLARE @Run_Date as int
DECLARE @DTL_CNT as int
DECLARE @TRL_CNT as int
DECLARE @File_Cnt as int
DECLARE @Vend_Name as varchar(20)
DECLARE @File_Name as varchar(255)
DECLARE @TRL_Run_Date as int

---- *** XXX
SET @Run_Date = Convert(Varchar(10),Getdate(),112)
Print @Run_Date
Set @File_Cnt = (Select count(*) FROM Trlr
WHERE TRLR.RunDate = @Run_Date And Trlr.Vendor_Name = 'XXX')
PRINT @File_Cnt

Declare vwCursor CURSOR FOR
SELECT RunDate, Vendor_Name, Vendor_File_Name, Dtl_Count
FROM Trlr
WHERE TRLR.RunDate = @Run_Date And Trlr.Vendor_Name = 'XXX'
open vwCursor
FETCH NEXT FROM vwCursor INTO @TRL_Run_Date, @Vend_Name, @File_Name, @TRL_CNT

SELECT A.RunDate, A.Vendor_File_Name, A.Vendor_Name, count(*) as RecCnt
into #tmp_recs_cnt_C
FROM Dtl A
Where A.Vendor_Name = 'XXX' and A.Vendor_File_Name = @File_Name
AND A.RunDate = @Run_Date
group by A.RunDate, A.Vendor_File_Name, A.Vendor_Name

select Vendor_Name from Dtl where Vendor_File_Name = @File_Name
select Vendor_Name from Dtl where Vendor_Name = 'XXX'
select RUndate from Dtl where rundate = @Run_Date
select * from #tmp_recs_cnt_C

INSERT INTO Trlr_Errors (RunDate, Vendor_File_Name, Vendor_Name, dtl_count, T.RecCnt)
SELECT A.RunDate, A.Vendor_File_Name, A.Vendor_Name, A.dtl_count,T.RecCnt
FROM Trlr A
INNER JOIN #tmp_recs_cnt_C T
ON A.RunDate=T.RunDate AND A.Vendor_File_Name=T.Vendor_File_Name AND A.Vendor_Name = T.Vendor_Name
--AND A.dtl_count<>@TRL_CNT
AND A.dtl_count=@TRL_CNT

FETCH NEXT FROM vwCursor INTO @TRL_Run_Date, @Vend_Name, @File_Name, @TRL_CNT
CLOSE vwCursor
DEALLOCATE vwCursor

Thanx

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-20 : 15:54:45

FETCH NEXT FROM vwCursor INTO @TRL_Run_Date, @Vend_Name, @File_Name, @TRL_CNT

WHILE @@FETCH_STATUS = 0
BEGIN
SELECT A.RunDate, A.Vendor_File_Name, A.Vendor_Name, count(*) as RecCnt
into #tmp_recs_cnt_C
FROM Dtl A
Where A.Vendor_Name = 'XXX' and A.Vendor_File_Name = @File_Name
AND A.RunDate = @Run_Date
group by A.RunDate, A.Vendor_File_Name, A.Vendor_Name

select Vendor_Name from Dtl where Vendor_File_Name = @File_Name
select Vendor_Name from Dtl where Vendor_Name = 'XXX'
select RUndate from Dtl where rundate = @Run_Date
select * from #tmp_recs_cnt_C

INSERT INTO Trlr_Errors (RunDate, Vendor_File_Name, Vendor_Name, dtl_count, T.RecCnt)
SELECT A.RunDate, A.Vendor_File_Name, A.Vendor_Name, A.dtl_count,T.RecCnt
FROM Trlr A
INNER JOIN #tmp_recs_cnt_C T
ON A.RunDate=T.RunDate AND A.Vendor_File_Name=T.Vendor_File_Name AND A.Vendor_Name = T.Vendor_Name
--AND A.dtl_count<>@TRL_CNT
AND A.dtl_count=@TRL_CNT

FETCH NEXT FROM vwCursor INTO @TRL_Run_Date, @Vend_Name, @File_Name, @TRL_CNT
END
CLOSE vwCursor
DEALLOCATE vwCursor

Trudye
Posting Yak Master

169 Posts

Posted - 2009-08-20 : 21:26:58

Thank you so much Russell for your response. But I have been talked out of usng a Cursor. I understand if I join the Trl and Dtl correctly I won't need a cursor. Not sure how to code that, any input you can provide would be helpful.

Thanx again,
Trudye

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources