Cursor Problem - SQL Server Forums

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

SQL Server Development (2000)

Cursor Problem

Author

Topic

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-04-01 : 15:11:52

I have a store procedure:

Alter Proc ProcChngLtrCreateCallRpt
@BegDate smalldatetime,
@EndDate smalldatetime
As
Declare @Err integer,@Note varchar(1000),@ChangeName varchar(50),
@NameF varchar(26), @NameMI char(1), @NameL varchar(26), @Street varchar(40),
@City varchar(40), @State char(2), @Zip varchar(10)
Declare @SQL table(vchrNumEnroll varchar(7),vchrUser varchar(30), vchrNameL varchar(30),
vchrNameF varchar(30),chrNameMI char(1),vchrStreet varchar(40), vchrCity varchar(26),
chrState char(2), vchrZip varchar(10))

Set nocount on
Select @Err=@@Error
Insert into @SQL
Select c.vchrNumEnroll,c.vchrUser,cust.vchrNameL,cust.vchrNameF,cust.vchrNameMI,
cust.vchrCustStreet, cust.vchrCustCity,cust.chrCustState,cust.vchrCustZip From tblChngs c
INNER JOIN tblCancelHold ch ON
c.sdtmChngDate=ch.sdtmCancel And c.vchrNumEnroll=ch.vchrNumEnroll INNER JOIN tblCustEnroll ce
ON ce.vchrNumEnroll=c.vchrNumEnroll INNER JOIN tblCust cust ON ce.intCustID=cust.intCustID
Where c.vchrItemName='Status' And c.vchrAfter>='2' And c.sdtmChngDate>=@BegDate
And c.sdtmChngDate<=@EndDate And ch.boolFC=0 And c.boolFCUpdated=0 And
ch.boolLttrPrinted=0

Declare CurChangeName Cursor Local Fast_Forward
For
Select vchrUser,vchrNameL,chrNameMI,vchrNameF,vchrStreet,
vchrCity,chrState,vchrZip from @SQL

Open CurChangeName
Fetch Next from CurChangeName
into @ChangeName,@NameL,@NameMI,@NameF,@Street,@City,@State,@Zip

While @@Fetch_Status = 0

Begin

Select @Note='Cancel/hold letter sent by' + space(1) + @ChangeName +'(- printed by' + space(1) + Current_User +' ) on ' + CONVERT(VARCHAR,GETDATE(), 101) + Char(13) + Char(10)
Select @Note=@Note + 'Letter type:'
Select @Note=@Note + 'Regular Cancellation' + Char(13) + Char(10)
Select @Note=@Note + 'To:'
Select @Note=@Note + @NameF
Select @Note=@Note + @NameMI
Select @Note=@Note + @NameL + Char(13) + Char(10)
Select @Note=@Note + @Street + Char(13) + Char(10)
Select @Note=@Note + @City + space(1)
Select @Note=@Note + @State + space(1)
Select @Note=@Note + @Zip

Begin Tran

Insert into tblCallRpt
(vchrNumEnroll,vchrCallNote,vchrUser,vchrCaller,sdtmDate)
Select vchrNumEnroll,@Note,vchrUser,'Batch Change
Report',Getdate() From @SQL
If @Err=0

Insert into tblSetupList
(vchrNumEnroll,vchrType)
Select vchrNumEnroll,'1'From @SQL
Fetch Next From curChangeName
into @ChangeName,@NameL,@NameMI,@NameF,@Street,@City,@State,@Zip

If @Err=0
Commit Tran
Else
Rollback Tran

End

close curChangeName
Deallocate curChangeName

My problem is: There're only 226 records in @SQL, but the cursor insert 51076 records which is (226 x 226)into tblCallRpt and tblSetupList.Anyone can tell what's wrong with my code?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-01 : 15:21:11

Why a cursor?

You are getting data into variables then for each row joining to @sql which is inserting 226 recs (per row).

could you just
Insert into tblCallRpt
(vchrNumEnroll,vchrCallNote,vchrUser,vchrCaller,sdtmDate)
Select vchrNumEnroll,vchrCallNote,vchrUser,'Batch Change
Report',Getdate() From @SQL

Insert into tblSetupList
(vchrNumEnroll,vchrType)
Select vchrNumEnroll,'1'From @SQL

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-01 : 15:26:01

"cursor problem" ... isn't that redundant ???

- Jeff

TAS
Yak Posting Veteran

65 Posts

Posted - 2004-04-01 : 15:37:41

There's no vchrNote column in @SQL. It's a column in tblCallRpt. I declare @Note as a variable because each vchrNumEnroll should have different note, which includes name, address...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-04-01 : 15:55:01

what are you trying to do? copy data from 1 table to two others, building up a long string in the process ??

As nigel says, all you need to do is build that expression for @Note in your INSERT statment. And do it straight from the SELECT


Insert into tblCallRpt
   (vchrNumEnroll, vchrNote ,vchrUser,vchrCaller,sdtmDate)
Select 
   vchrNumEnroll, Name + Address + char(13) + char(10) + 'blah blah' ,vchrUser,'Batch Change Report',Getdate() 
From 
   @SQL

in fact -- get rid of the whole @SQL table variable. just select right from your query, right into the other table ...


Insert into tblCallRpt
   (vchrNumEnroll, vchrNote ,vchrUser,vchrCaller,sdtmDate)
Select 
   vchrNumEnroll, Name + Address + char(13) + char(10) + 'blah blah' ,vchrUser,'Batch Change Report',Getdate() 
From 
   (the entire SQL statement for @SQL here) A

Why overcomplicate things?

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources