Can anyone see why this doesn't work ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

Can anyone see why this doesn't work ?

Author

Topic

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-10-25 : 10:48:19

I have the following code in a Stored Procedure, which works fine if the employee has an entry in tbl_Holidays already for that year, however if they do not, it doesn't work.

I can't see why though.

If this code is fine, then obviously it looks like the problem could be with my function, but I thought before going round the houses looking at that, I'd check with the experts on here.


IF NOT EXISTS (SELECT * FROM tbl_Holidays WHERE EmployeeNumber = @EmployeeNo AND HolidayDate = @BankHolidayDateConverted)
		BEGIN
			INSERT INTO tbl_Holidays(EmployeeNumber,ReceivedOn,HolidayDate,AM,PM,NumberOfDays,NumberOfHours,Extra_Reason)
			SELECT @EmployeeNo, GETDATE(),@BankHolidayDateConverted, 0,0, 1,dbo.fn_Employee_Working_Day(@EmployeeNo, @BankHolidayDate),'Bank Holiday'
			WHERE dbo.fn_Employee_Working_Day(@EmployeeNo, @BankHolidayDateConverted) <> 0	
		END
	ELSE
		BEGIN
			UPDATE tbl_Holidays
			SET NumberOfHours = dbo.fn_Employee_Working_Day(@EmployeeNo, @BankHolidayDateConverted)
			WHERE (EmployeeNumber = @EmployeeNo) AND (HolidayDate = @BankHolidayDateConverted)
		END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 10:54:47

[code]IF EXISTS (SELECT * FROM tbl_Holidays WHERE EmployeeNumber = @EmployeeNo AND HolidayDate = @BankHolidayDateConverted)
UPDATE tbl_Holidays
SET NumberOfHours = dbo.fn_Employee_Working_Day(@EmployeeNo, @BankHolidayDateConverted)
WHERE EmployeeNumber = @EmployeeNo
AND HolidayDate = @BankHolidayDateConverted
ELSE
IF dbo.fn_Employee_Working_Day(@EmployeeNo, @BankHolidayDateConverted) <> 0
INSERT tbl_Holidays
(
EmployeeNumber,
ReceivedOn,
HolidayDate,
AM,
PM,
NumberOfDays,
NumberOfHours,
Extra_Reason
)
SELECT @EmployeeNo,
GETDATE(),
@BankHolidayDateConverted,
0,
0,
1,
dbo.fn_Employee_Working_Day(@EmployeeNo, @BankHolidayDate), -- Where is @BankHolidayDateConverted variable?
'Bank Holiday'[/code]

E 12°55'05.25"
N 56°04'39.16"

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-10-25 : 11:03:21

It's further up - basically all it does is convert the actual date to a varchar(10) as this is what the table requires.

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-10-25 : 11:07:14

the code for it is :


SET @BankHolidayDateConverted = CONVERT(varchar(10), @BankHolidayDate, 103)

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-10-25 : 11:10:44

PS Peso - your code does exactly the same as mine, which leads me to believe it's something to do with the function.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-25 : 11:13:02

Maybe. I don't dare to guess...

E 12°55'05.25"
N 56°04'39.16"

Jonny1409
Posting Yak Master

133 Posts

Posted - 2007-10-25 : 11:24:39

Sorry Peso, I know you can't see my function - I don't expect you to have to guess.
I was going to put it up here, but it's huge.

Anyhow, I've been doing some more digging, and I think it my BankHolidayConvertedDate that is the problem.
Here's why :

If I put a PRINT statement in as per :


PRINT dbo.fn_Employee_Working_Day(@EmployeeNo, @BankHolidayDateConverted)

This shows '0'

However, if I execute the function in Query Analyzer as per :


SELECT [Staff_Database].[dbo].[fn_Employee_Working_Day]('989898','01/01/2008')

This shows as '8.75' which is correct.

If I then add a PRINT statement of :


PRINT dbo.fn_Employee_Working_Day('989898', '01/01/2008')

This shows as '8.75' also - which leads me to believe it has to be the BankHolidayDateConverted causing the problem.
However, in QA when I use a PRINT statement, this shows as 01/01/2008 as it should.

Do you have any ideas ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-26 : 02:22:27

ALWAYS pass DATETIME parameters as

1) DATETIME
2) If for some obscure reason DATETIME is not available, use VARCHAR in format YYYYMMDD

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources