IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetRecipePreparat]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetRecipePreparat]
GO

--SET ANSI_NULLS ON
--GO

--SET QUOTED_IDENTIFIER ON
--GO
---- =============================================
---- Author:		Marat Zigangaraev
---- Create date: 02.07.2010
---- Description: Возвращает препарат рецепта
---- =============================================
--CREATE FUNCTION [dbo].[fn_GetRecipePreparat] 
--(
--	@RecipeID uniqueidentifier
--)
--returns varchar(255)
--as
--begin
--	declare @ret varchar(255)
--	declare @type int
--	select @type = type_ from [AKUZ].T_MEDICAL_RECORD where MedicalRecordID = @RecipeID
--	if @type = 607
--	begin
--		select @ret = ISNULL(T.NameLat + ' (' + M.NameLat + ')', M.NameLat)
--		from [AKUZ].T_RECIPE_FEDERAL R
--			inner join [AKUZ].T_MNN M on M.Code = R.Mnn
--			left join [AKUZ].T_TRN T on T.Code = R.Trn
--		where R.MedicalRecordID = @RecipeID
--	end
--	else if @type = 673
--	begin
--		select @ret =
--		case
--		 when (T.NameLat is null and M.NameLat is null) then ISNULL(T2.NameLat + ' (' + M2.NameLat + ')', M.NameLat)
--		 else ISNULL(T.NameLat + ' (' + M.NameLat + ')', M.NameLat)
--		end
--		from [AKUZ].T_RECIPE_REGIONAL R
--			left join [AKUZ].T_MNN_REGIONAL M on R.MnnRegional = M.MnnRegionalID
--			left join [AKUZ].T_TRN_REGIONAL T on R.TrnRegional = T.TrnRegionalID
--			left join [AKUZ].T_MNN M2 on M2.Code = R.Mnn
--			left join [AKUZ].T_TRN T2 on T2.Code = R.Trn
--		where R.MedicalRecordID = @RecipeID
--	end
--	return @ret
--end
--GO
