Jeff,

I believe it is CAST which is failing - why do you need to cast to an integer?

IF CAST(@tcJobNo AS int) = 0

You can change it to

IF ISNUMERIC(@tcJobNo) = 1 and cast(@tcJobNo as int) = 0

What exactly do you want to test? If you're passing all 00000000 ?


>I was experimenting with a Scalar-Valued function to return the total labor value for a job. To do this I created the following function.
>
>

>USE [JCS]
>GO
>SET ANSI_NULLS ON
>GO
>SET QUOTED_IDENTIFIER ON
>GO
>
>ALTER FUNCTION [dbo].[GetLaborTotal] 
>(
>	-- Job Number to be passed into function
>	@tcJobNo Char(6)
>)
>RETURNS Numeric(12,2)
>AS
>BEGIN
>
>	DECLARE @nRetVal Numeric(12,2)
>
>	IF CAST(@tcJobNo AS int) = 0
>		SET @nRetVal = 0.00
>	ELSE
>		BEGIN
>		DECLARE @nTOTOH Numeric(12,0)
>
>		-- Get sum of labor
>		SET @nTOTOH = (SELECT sum(nHours * nRate)
>							FROM dbo.JCProd
>							WHERE cJobno = @tcJobNo)
>		
>		-- Return the total of hours multiplied by rate
>		SET @nRetVal = ISNULL(@nTOTOH, 0)
>	END
>
>	RETURN @nRetval
>
>END
>

>
>I was then trying to test it with the following code.
>
>

>SELECT JCMAST.cjobno, Dbo.GetLaborTotal(JCMAST.cjobno) AS nTOTOH FROM DBO.JCMAST
>

>
>The problem is I get the following error when testing. It appears to the related to the cJobno parameter. The cJobNo field in the table is defined as char(6) and contains values such as "001056" and "main ". If I specifically pass a value such as "001056" it works.
>
>Msg 245, Level 16, State 1, Line 1
>Conversion failed when converting the varchar value 'main ' to data type int.
>
>What am I doing wrong here? What's my best avenue for getting more proficient with these types of functions? Any book suggestions?
>
>TIA
>Jeff