IF EXISTS ( SELECT * 
            FROM   sysobjects 
            WHERE  id = object_id(N'dbo.CF_Traducir') 
                   and type = 'FN')
BEGIN
    DROP FUNCTION dbo.CF_Traducir
END
GO

CREATE FUNCTION [dbo].[CF_Traducir](@pFrase VARCHAR(4000), @pIdioma SMALLINT)
RETURNS VARCHAR(4000)
AS
BEGIN

IF ISNULL(@pIdioma,0) = 0 RETURN @pFrase 


DECLARE @Frase		VARCHAR(4000)
DECLARE @Separador	CHAR(1)
DECLARE @Palabras	TABLE	(Id INT IDENTITY(1,1),
							Data NVARCHAR(4000)) 
DECLARE @Contador		INT

SET @Frase =ISNULL((SELECT Traduccion FROM Tab_Idiomas_Trad WHERE Frase=@pFrase AND Id_Idioma=@pIdioma), '');

IF(@Frase <> '') RETURN @Frase 

IF(@Frase = '')
BEGIN
	SET @pFrase		= @pFrase
	SET @Separador	= ' '

	SET @Contador = 1
	WHILE (Charindex(@separador, @pFrase)>0)
	BEGIN
		INSERT INTO @Palabras (data)
		SELECT Data = LTRIM(RTRIM(SUBSTRING(@pFrase,1, CHARINDEX(@separador, @pFrase)-1)))

		SET @pFrase = SUBSTRING(@pFrase, CHARINDEX(@separador, @pFrase)+1, LEN(@pFrase))
		SET @Contador = @Contador + 1
	END

	SET @pFrase = ISNULL((SELECT Traduccion FROM Tab_Idiomas_Trad WHERE Frase=@pFrase AND Id_Idioma=@pIdioma), @pFrase);


	INSERT INTO @Palabras (data)
	SELECT Data = LTRIM(RTRIM(@pFrase))


	SELECT @frase = COALESCE(@frase + ' ', '') + data  FROM @Palabras
END



RETURN LTRIM(RTRIM(@frase))
END
/*
select dbo.SF_Traduccion('administrador de socios')
*/
