User-defined function help

[eao]

I've got the following function defined:

USE MagicTSD
GO

ALTER FUNCTION dbo.ProjectTime_GetProjectID (@WONum int)
RETURNS nvarchar(40)

BEGIN
DECLARE @ProjID nvarchar(40)

SET @ProjID = 'No ID'

IF ( NOT (SELECT [HDNUM] FROM [_SMDBA_].[_WORKORD_] as W WHERE W.[SEQUENCE] = @WONum) IS NULL)
	SET @ProjID = 
		(	SELECT PROJECTID
			FROM [_SMDBA_].[PROJECT] as P INNER JOIN [_SMDBA_].[_TELMASTE_] as I on I.[DM_SEQ_PROJECTS] = P.[SEQUENCE]
			AND I.[SEQUENCE] = (SELECT [HDNUM] FROM [_SMDBA_].[_WORKORD_] as W WHERE W.[SEQUENCE] = @WONum)
		)
 
IF ( NOT (SELECT [DM_WO_SEQ_CHANGEREQUEST] FROM [_SMDBA_].[_WORKORD_] as W WHERE W.[SEQUENCE] = @WONum) IS NULL)
	SET @ProjID =
		(	SELECT PROJECTID
			FROM [_SMDBA_].[PROJECT] AS P 
			WHERE P.[SEQUENCE] = 	(
						SELECT [DM_SEQ_PROJECTS] FROM [_SMDBA_].[_TELMASTE_] WHERE SEQUENCE = 
							(
							SELECT [DM_CR_SEQ_SERVICEREQUEST] FROM [_SMDBA_].[_CHANGE_] AS C WHERE C.[SEQUENCE] = 
								(
								SELECT [SEQ_CHANGE:] FROM [_SMDBA_].[_WORKORD_] as W WHERE W.[SEQUENCE] = @WONum
								)
							)
						)
		)

RETURN @ProjID
END

If I run:

SELECT dbo.ProjectTime_GetProjectID (436826) from DUAL

I get 'No ID', which is not the expected result.

If I run:

(SELECT PROJECTID
FROM [_SMDBA_].[PROJECT] AS P 
WHERE P.[SEQUENCE] = 
(SELECT [DM_SEQ_PROJECTS] FROM [_SMDBA_].[_TELMASTE_] WHERE SEQUENCE = 
	(SELECT [DM_CR_SEQ_SERVICEREQUEST] FROM [_SMDBA_].[_CHANGE_] AS C WHERE C.[SEQUENCE] = 
		(SELECT [SEQ_CHANGE:] FROM [_SMDBA_].[_WORKORD_] as W WHERE W.[SEQUENCE] = 436826))))

I get 'Demand Management', which is the expected result. Any idea why my UDF is not returning the correct value? Thanks for the help.

 

[Otto]

Perhaps, the first select give you the right result, but you override it in the second.

 

[SQLDenis]

>>USE MagicTSD




Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[eao]

Thanks Otto. You got me headed down the right path. My secondary IF... condition was looking at the wrong field. I updated it and it now works properly.