470,596 Members | 1,555 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,596 developers. It's quick & easy.

Count Parameter not right

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
1 1557
Motoma
3,237 Expert 2GB
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.

Similar topics

1 post views Thread by Andreas Paasch | last post: by
22 posts views Thread by MP | last post: by
13 posts views Thread by craigchalmers | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.