
IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[FNMED_TABLETDOKTORBILGILERI]') AND xtype in (N'FN', N'IF', N'TF'))
	DROP FUNCTION [dbo].[FNMED_TABLETDOKTORBILGILERI]
GO
CREATE FUNCTION [dbo].[FNMED_TABLETDOKTORBILGILERI](@SUBEID INT, @TABLETIP NVARCHAR(15))
RETURNS @TABLETDOKTOR TABLE 
(
	DOKTORID INT NOT NULL,
	UNVAN NVARCHAR(10) NULL,
	ADI NVARCHAR(100) NULL,
	UZMANLIK NVARCHAR(100) NULL, 
	BOLUM NVARCHAR(100) NULL,
	BIRIM NVARCHAR(100) NULL,
	RESIM [image] NULL,
	TABLETFOTO NVARCHAR(300)
) 
AS
BEGIN

  INSERT INTO @TABLETDOKTOR (DOKTORID, UNVAN, ADI, UZMANLIK, BOLUM, BIRIM, TABLETFOTO)

	SELECT DRID, DRUNVAN, DRADI, DRUZMANLIKADI, BOLUMADI, POLBIRIMADI, TABLETFOTO FROM
	(
		SELECT TOP 1 VWDRKIMLIK.DRID, VWDRKIMLIK.DRUNVAN, VWDRKIMLIK.DRADI, VWDRKIMLIK.DRUZMANLIKADI, VWDRKIMLIK.BOLUMADI, VWDRKIMLIK.POLBIRIMADI, UFOTOGRAF.TABLETFOTO, USERSESSION.LOGOUTTIME
		FROM USERSESSION WITH(NOLOCK)
		JOIN TTABLET WITH(NOLOCK) ON TTABLET.SUBEID=USERSESSION.SUBEID AND TTABLET.MEDISOFTPCNAME= USERSESSION.COMPUTERNAME
		JOIN [USER]  USR WITH(NOLOCK) ON USR.KODU=USERSESSION.USERKODU
		JOIN VWDRKIMLIK ON VWDRKIMLIK.DRID = USR.DRID
		LEFT JOIN UFOTOGRAF WITH(NOLOCK) ON UFOTOGRAF.KODU=USR.KODU
		WHERE	 USERSESSION.LOGINTIME>CONVERT(DATE,GETDATE())
			AND TTABLET.TABLETIP=@TABLETIP
			AND TTABLET.SUBEID=@SUBEID
		ORDER BY LOGINTIME DESC  
	) AS TBLLGN
	WHERE LOGOUTTIME IS NULL 
		
  
  
   RETURN 
END
GO
