May 29, 2013 at 2:38 pm
I have a stored procedure which processes clock events from employees times for compliance with California's labor laws. It essentially identifies any violations and updates the record with the violation which will then be printed on a report and paid by our company.
Anyway, There are some identified errors on clock events that are not being caught by the code. It used to run correctly until SP2 was applied to our SQL2K8 installation, 64 bit on Windows Server 2008R2 64bit. But since then, it doesn't appear to run the code snippet below properly. I say appears not to run because as far as I know it did prior.
What I found is in the code snippet below, that code isn’t being executed when it should. During debugging, I put in some print statements, and it began working. I kept removing the print statements until it stopped working again to find out where the issue was/is.
I have it down to one line, the line where the print statement is commented out. If I leave it commented out, the code below it won’t execute, if I uncomment it, it runs fine. Totally bizarre.
I’ve checked for hidden control characters in a hex editor, tried deleting and retyping those lines, checked for hanging/missing Begin….End block statements, etc. I can’t figure it out.
I also realize there is probably some better ways to do this processing, however, the business rules are written in such a way that i have to process record by record as I have compare to the prior shift and check break times between shifts, etc. 🙂
.
.
.
Else
--Different Date, so first check for no break condition, update record,
--then reset hold variables
Begin
If @ShiftHours >= '05:00:00' OR (@ShiftHours >= '05:00:00' AND @RecsProcessed >= @RecCnt)
[highlight=#ffff11] --print ''[/highlight]
Begin
If @HoldHoursWorked > '05:01:00'
Begin
--update the entries for the current hold record
Update stage_EmployeeCheckInOut Set ViolationType = ViolationType + 'No Meal Period, ',
ViolationPay = ViolationPay + 1, Violation = 'Y'
Where RecordID = @HoldRecID
End
End
--at this point, this variable should only hold splitshift violations if applicable.
If @strViolation <> ''
Begin
Update stage_EmployeeCheckInOut Set ViolationType = ViolationType + @strViolation,
ViolationPay = ViolationPay + @intViolationCnt, Violation = 'Y'
Where RecordID = @HoldRecID
End
--reset shift hours since we are on a different date for the employee
Set @ShiftHours = @HoursWorked
Set @BreakTime = '00:00:00'
--get record count for this day
Select @RecCnt = COUNT(*) from stage_EmployeeCheckInOut
Where SchoolID = @SchoolID AND EmplID = @EmplID AND SSN = @SSN AND ProcareEmployeeID = @ProcareEmployeeID
AND dbo.GetDateOnly(CheckIn) = dbo.GetDateOnly(@CheckIn)
Set @RecsProcessed = 1
End
Thanks,
Pat
May 29, 2013 at 3:09 pm
Hi and welcome to the forums. Unfortunately your description of things like "doesn't seem to work" does not really provide any kind of details. We can only see a portion of the code and we have no idea what the data structures look like or what this is attempting to do. I am guessing this is inside of a cursor, as you eluded this could probably be done another way.
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 29, 2013 at 3:21 pm
Thanks for the reply, I really appreciate it.
What I'm trying to say is that in the code snippet provided, the Update statement will not get executed unless I uncomment the Print statement above it. If I comment out the print statement, the update never runs.
Its honestly the strangest behavior I've seen. I can't understand why including a print statement would cause the IF statement to be evaluated as true and then the Update statement is executed as expected.
I included a lot of print statements to try and diagnose the problem, and have removed them all from the procedure with the exception of the one in the code snippet.
I guess what I'm asking is can anyone think of any reason which would cause this behavior? I have been running the same 4 records through, and if I leave the print statement commented, it never executes the update statement. If I uncomment the print statement, the update statement does get executed and the results are as expected. Just can't figure out the reason the Print statement causes the expected execution.
May 29, 2013 at 3:34 pm
progers-677549 (5/29/2013)
Thanks for the reply, I really appreciate it.What I'm trying to say is that in the code snippet provided, the Update statement will not get executed unless I uncomment the Print statement above it. If I comment out the print statement, the update never runs.
Its honestly the strangest behavior I've seen. I can't understand why including a print statement would cause the IF statement to be evaluated as true and then the Update statement is executed as expected.
I included a lot of print statements to try and diagnose the problem, and have removed them all from the procedure with the exception of the one in the code snippet.
I guess what I'm asking is can anyone think of any reason which would cause this behavior? I have been running the same 4 records through, and if I leave the print statement commented, it never executes the update statement. If I uncomment the print statement, the update statement does get executed and the results are as expected. Just can't figure out the reason the Print statement causes the expected execution.
Because your print statement is before the begin.
If @ShiftHours >= '05:00:00' OR (@ShiftHours >= '05:00:00' AND @RecsProcessed >= @RecCnt)
--print ''
begin
...
end
If you uncomment the print statement it will only print when the condition is true and the code after it will always run.
Move the print statement inside the begin block. Remember that begin end blocks are not required and can be used anywhere.
In essence this is what you have coded.
if exists(select * from sys.objects)
select 'yeap'
begin
select top 5 * from sys.objects
end
When instead what you really want is
if exists(select * from sys.objects)
begin
select 'yeap'
select top 5 * from sys.objects
end
Make sense now?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 29, 2013 at 3:38 pm
I can understand what you are saying. My issue is, that I don't need the print statement at all and it truly isn't part of the actual code, I had just added to help debug why the update statement was never being executed. What I found is that whenever there is a print statement exactly on that line, the update statement does execute. I am able to verify the conditions of the 2nd IF statement are met so the Update statement should run, but doesn't unless I have that print statement there. I only put it in for debugging and found that by including it, it allows the statements to evaluate to true and then the update statement gets executed.
May 29, 2013 at 3:47 pm
progers-677549 (5/29/2013)
I can understand what you are saying. My issue is, that I don't need the print statement at all and it truly isn't part of the actual code, I had just added to help debug why the update statement was never being executed. What I found is that whenever there is a print statement exactly on that line, the update statement does execute. I am able to verify the conditions of the 2nd IF statement are met so the Update statement should run, but doesn't unless I have that print statement there. I only put it in for debugging and found that by including it, it allows the statements to evaluate to true and then the update statement gets executed.
From the code you posted adding the print does not change the evaluation of the IF statement. It will simply run the update statement. By adding the print statement you are changing the logic. The print statement will be executed when the condition is true but the update will run every time.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 29, 2013 at 3:51 pm
Ok, wow, that's amazing. I thought that it would have still tried to evaluate the subsequent IF statement, and if that condition was true it would execute the update statement. So by putting a print statement there, its "bypassing" that subsequent if statement and begin..end block and executing the update?
I appreciate you taking time to help me with this, I really do. It's just a behavior I've not seen before...
May 29, 2013 at 4:05 pm
I believe the order of operations is what's confusing you, along with a secondary syntax for IF.
There are two ways to write an IF:
IF 1=1
PRINT 'One Command Only Active'
BEGIN
PRINT This is ALWAYS Active'
END
IF 1=1
BEGIN
PRINT 'Batch is Active 1'
PRINT 'Batch is Active 2'
END
Play with that a bit and it may help you understand further what Sean was trying to show you about the operations order.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply