Jeff,
I believe it is CAST which is failing - why do you need to cast to an integer?
IF CAST(@tcJobNo AS int) = 0You 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]
>(
>
> @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)
>
>
> SET @nTOTOH = (SELECT sum(nHours * nRate)
> FROM dbo.JCProd
> WHERE cJobno = @tcJobNo)
>
>
> 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
If it's not broken, fix it until it is.
My Blog