June 3, 2003 at 9:49 am
This is a syntax related question, I am obviously doing somethig foolish as I never get into the conditions for the IF. I was hoping one of you could point out where I have gone wrong...
I need to assign a value to @percent based upon a date. all dates are declared as datetime. Thanks in advance!
Here is how I THOUGHT it should work.
Set @date = '5-15-2003'
Set @Branch = 'LJN'
Set @Start = '9-1-2001'
Set @change1 = '9-15-2001'
Set @change2 = '10-15-2002'
Set @change3 = '10-15-2003'
Set @type = 'New'
Select @GC1 = GC_GrossComm from BranchSummary where BranchId = @Branch and BatchId = @b_date
Select @GC2 = GC_PayOut from BranchSummary where BranchId = @Branch and BatchId = @b_date
Select @OC1 = Other_GrossComm from BranchSummary where BranchId = @Branch and BatchId = @b_date
Select @OC2 = Other_PayOut from BranchSummary where BranchId = @Branch and BatchId = @b_date
If @date between @change1 and @change2
Begin
set @percent = .075
end
else if @date between @change2 and @change3
begin
set @percent = .04
end
else
begin
set @percent = .02
end
select @percent
Set @LJN_DV = ((@GC1-@GC2) + (@OC1-@OC2)) * @percent
Select @LJN_DV
June 4, 2003 at 7:20 am
Interesting. I return a value of 0. Thanks for looking at it! My error must be elsewhere.
June 4, 2003 at 7:47 am
What do you have @percent declared as?
June 4, 2003 at 7:52 am
As a check to see whether you are in the if statements you could put in print statements in the different if conditions to see whether the code actually enters the if statements.
Also, are you declaring @percent as decimal or int? If it is int (or tinyint etc) you will get 0.
Jeremy
June 4, 2003 at 2:04 pm
BOL: BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.
Thus @date equals @change2 both .075 and .04 will be valid. In your example .075 will be assigned.
June 4, 2003 at 9:51 pm
No it won't, 5/15/2003 is not between 9/15/2001 and 10/15/2002.
June 5, 2003 at 7:35 am
Ok, I am in the IF statement, but @percent returns 0 even though I set @percent to .04 where it hits. example below:
Declare @B_Date varchar (30)
Set @B_Date = '5-15-2003-FISERV BLOTTER.TXT'
Declare @A_Payout Money
Declare @D_Payout Money
Declare @Branch varchar (3)
declare @date datetime
Set @date = '5-15-2003'
Select @date
Declare @percent decimal
Declare @change1 datetime
Declare @change2 datetime
Declare @change3 datetime
Declare @start datetime
Declare @type varchar (3)
Set @Branch = 'LJN'
Set @Start = '9-1-2001'
Set @change1 = '9-15-2001'
Set @change2 = '10-15-2002'
Set @change3 = '10-15-2003'
Set @type = 'New'
select @change1
select @change2
select @change3
If @date between @change1 and @change2
Begin
set @percent = .075
select @percent + 1
end
else if @date between @change2 and @change3
begin
set @percent = .04
select @percent + 2
end
else
begin
set @percent = .02
select @percent +3
end
select @percent
This returns @percent = 2 in the If statement and as 0 after the IF. Can I not use SET here? As always, any help would be greatly appreciated.
June 5, 2003 at 8:09 am
You are not giving your decimal datatype any precision or scale. Try declaring like this
Declare @percent decimal(4,2)
June 5, 2003 at 8:49 am
AH! You are officially the man. That works great!
June 6, 2003 at 2:44 am
My two cents:
When debugging SPs' in SS there is an extremely important new feature in QA 2000. Debug. Open up the object browser,rightclick on your SP and choose DEBUG. 😉 But remember NOT to do this on a production server.
Regards, Hans!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply