By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,939 Members | 1,600 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,939 IT Pros & Developers. It's quick & easy.

Count Parameter not right

P: 1
Having an issue with the @Count parameter in the following and I'm rather new to this so I may have missed something:

Create Procedure AAA
As
Declare Customer Cursor Fast_Forward
For
Select ID, DateFld from Table1 where type = 4 and ID In
(Select ID from Table1 where type_ID = 8)
Declare @Expiry Datetime
Declare @CustomerID Varchar(30)
Declare @Count int
Open Customer
Fetch Customer into @CustomerID, @Expiry
while (@@Fetch_Status <> -1)
Begin
Set @Count = (Select Count(*) from Followuptbl where
Datediff(Day,Create_Date,@Expiry) < 366 and ID = @CustomerID)
Update Table1 Set totalfollowupfld = @Count where ID = @CustomerID and Type = 5
Fetch Customer INTO @CustomerID, @Expiry
End
Close Cursor
Deallocate Cursor

I printed the values of @CustomerID, @Expiry and @Count. The procedure pulls in the correct number of Rows, the values in @CustomerID & @Expiry are as expected but @Count is always 0 so nothing changes
Tried running the Select Count statement by itself and filled in the values for @Expiry and @CustomerID. I got the expected Count. This is supposed to track the number of inquiries made during the Customers subscription and update an Integer Column. The Database is not properly normalized hence the funny select statement at the top to get ID's tagged as Active and having an expiry date (Exists on 2 lines in the Table) Any ideas?
Jun 29 '07 #1
Share this Question
Share on Google+
1 Reply


Motoma
Expert 2.5K+
P: 3,235
In the future, please use the code tags.

I think all you need is this:
Expand|Select|Wrap|Line Numbers
  1. Select @Count = Count(*) from Followuptbl where Datediff(Day,Create_Date,@Expiry) < 366 and ID = @CustomerID
  2.  
Having an issue with the @Count parameter in the following and I'm rather new to this so I may have missed something:

Create Procedure AAA
As
Declare Customer Cursor Fast_Forward
For
Select ID, DateFld from Table1 where type = 4 and ID In
(Select ID from Table1 where type_ID = 8)
Declare @Expiry Datetime
Declare @CustomerID Varchar(30)
Declare @Count int
Open Customer
Fetch Customer into @CustomerID, @Expiry
while (@@Fetch_Status <> -1)
Begin
Set @Count = (Select Count(*) from Followuptbl where
Datediff(Day,Create_Date,@Expiry) < 366 and ID = @CustomerID)
Update Table1 Set totalfollowupfld = @Count where ID = @CustomerID and Type = 5
Fetch Customer INTO @CustomerID, @Expiry
End
Close Cursor
Deallocate Cursor

I printed the values of @CustomerID, @Expiry and @Count. The procedure pulls in the correct number of Rows, the values in @CustomerID & @Expiry are as expected but @Count is always 0 so nothing changes
Tried running the Select Count statement by itself and filled in the values for @Expiry and @CustomerID. I got the expected Count. This is supposed to track the number of inquiries made during the Customers subscription and update an Integer Column. The Database is not properly normalized hence the funny select statement at the top to get ID's tagged as Active and having an expiry date (Exists on 2 lines in the Table) Any ideas?
Jun 29 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.