DROP FUNCTION IF EXISTS dbo.fnGetFelhaszaloCim;  
GO

CREATE FUNCTION [dbo].[fnGetFelhaszaloCim] (@pFelhasznaloId int)
RETURNS TABLE 
RETURN (
  SELECT TOP 1
    c.ID, C_IRANYITOSZAM, C_VAROS, C_KOZTERULET, C_KOZTERULETJELLEGENEV, C_HAZSZAM, C_EMELET, C_AJTO, c.C_ORSZAG, ot.C_ISOKOD, dic.C_NAME
  FROM T_CIM_OSSZES c
  JOIN T_ORSZAGTIPUS_OSSZES ot on ot.ID = c.C_ORSZAG and c.C_TANEVID = ot.C_ALTANEVID
  JOIN T_DICTIONARYITEMBASE_OSSZES dic on dic.ID = c.C_ORSZAG and dic.C_TANEVID = ot.C_ALTANEVID
  WHERE
    c.C_FELHASZNALOID = @pFelhasznaloId AND c.TOROLT = 'F' AND c.C_GONDVISELOID IS NULL
  ORDER BY
    CASE C_CIMTIPUSA
      WHEN 907 THEN 1
      WHEN 909 THEN 2
      WHEN 908 THEN 3
      WHEN 910 THEN 4
      WHEN 8667 THEN 5
    END
);
GO