I can create a user defined function to get the value but not sure how I can use that either?

CREATE FUNCTION dbo.udf_retained_nomcode()

RETURNS @ret TABLE (col VARCHAR (16))

AS

BEGIN

DECLARE @retained varchar(16);

SELECT @retained=nomcode from ctlfil where recid=24;

INSERT INTO @ret SELECT @retained;

RETURN;

END;

GO

SELECT * FROM dbo.udf_retained_nomcode();

GO