Click here to Skip to main content
15,885,878 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
SQL
FUNCTION [dbo].[GetTotalSalary1_func] 
(
	@emplID int,
	@month VARCHAR(50) = NULL
)
RETURNS int
AS
BEGIN
	Declare @BSalaryHour int, @TotalSalary int, @TotalHoursWorked int
		     Set @TotalHoursWorked = (Select OverallTime from MonthlyRecord where EmplID = @emplID AND Month = @month )
		Set @TotalSalary = @BSalaryHour * @TotalHoursWorked
		--------------------------------------------------------------------------------------
	
	-- Return the result of the function
	RETURN @TotalSalary

END

select dbo.GetTotalSalary1_func(5,'2013-dec')

it throws error on this line:

SQL
Set @TotalSalary = @BSalaryHour * @TotalHoursWorked

when @totalHoursWorked returned are like this 192:22 or 105:01 i guess bcz it can't multiply :22, :01 part with @BsalaryHour.

so how to do it ?

Note:

OverallTime is basically varchar(13)
Posted

1 solution

Best way is not to store numeric values as strings: Either record it as a floating point value in hours (so 6 hours 15 minutes is 6.25) or as a number of minutes worked.

Every time you store numbers, dates, or any other "processable" value in string based columns, you end up giving yourself massive problems when you try to use them. It's possible - you would have to parse the input and allow for all sorts of errors - but it's complex, and a lot, lot easier to just ensure your database contains usable values in the first place.
 
Share this answer
 
Comments
Hunain Hafeez 20-Feb-14 5:51am    
sir please if you could provide help in code ? i am tired of doing and trying it
Hunain Hafeez 20-Feb-14 5:52am    
sir db has high number of interconnected tables, store pro, functions, views so now changing them will result in high level inconsistency so if you could parse my code ?
OriginalGriff 20-Feb-14 5:55am    
If this column is used already so many times then the existing code will already be doing it - so copy it out of there.
If it isn't, then write a conversion app which converts them to a numeric field and highlights the errors - because this is currently very poor design and doing the parse with all the checking it needs is a PITA!
Maciej Los 20-Feb-14 15:35pm    
Great answer! ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900