Replace function... is it possible?

Question

Replace function... is it possible?

PiMané

SSChampion

Points: 13965
More actions
January 31, 2013 at 10:53 am

#273694

Hi,
I've a customers table and a contacts table related to customers... There's a query that returns the customers information and a column with all the emails from the contacts table related to the customer.
To do so I created the following function:
CREATE FUNCTION [dbo].[getContactoGerencia2] (
@Cliente NVarchar(12),
@TipoCont Tinyint
) RETURNS Varchar(512)
AS
BEGIN
DECLARE @Email Varchar(512)
SET @Email = ''
IF @TipoCont = 1
SELECT @Email = @Email + ';' + ISNULL(Email, '') FROM LinhasContactoEntidades WHERE Entidade = @Cliente AND TipoContacto = 'GERENCIA'
ELSE
SELECT @Email = @Email + ';' + ISNULL(Email, '') FROM LinhasContactoEntidades WHERE Entidade = @Cliente AND TipoContacto = 'RESP ADMINISTRA'
RETURN @Email
END
I then though using XML to get the result, with a subquery:
SELECT c.cliente, (select ISNULL(email, '') + ';' from linhascontactoentidades lce where lce.entidade = c.cliente AND tipoContacto = 'GERENCIA' FOR XML PATH('')) from clientes c
but since I have to use the objects that exist in the database and can't change the application I have to create a function with the XML. I got:
CREATE FUNCTION getContactoGerencia3 (@Cliente VARCHAR(12), @TipoContacto INT)
RETURNS TABLE
RETURN
select email = ISNULL(email, '') + ';' from linhascontactoentidades lce where lce.entidade = @Cliente AND tipoContacto = CASE WHEN @TipoContacto = 1 THEN 'GERENCIA' ELSE 'RESP ADMINISTRA' END FOR XML PATH('')
but it gives the error: "CREATE FUNCTION failed because a column name is not specified for column 1."
How can I give an alias to the column?!?
Thanks,
Pedro

If you need to work better, try working less...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

mickyT SSChampion Points: 10360 More actions · Answer 1

There are probably better people to replay to this than me, but I would say that you need to make this a subquery and alias the result from that. I tried the following and it seemed to do the trick

CREATE FUNCTION getContactoGerencia3 (@Cliente VARCHAR(12), @TipoContacto INT)

RETURNS TABLE

RETURN

SELECT email = ((

SELECT ISNULL(email, '') + ';'

FROM linhascontactoentidades lce

WHERE lce.entidade = @Cliente

AND tipoContacto = CASE WHEN @TipoContacto = 1 THEN 'GERENCIA' ELSE 'RESP ADMINISTRA' END

FOR XML PATH(''),TYPE).value('text()[1]','varchar(8000)'))

PiMané SSChampion Points: 13965 More actions · Answer 2

Thanks,

It works fine... but TYPE is very "heavy" so I tried something else and got this:

ALTER FUNCTION getContactoGerencia3 (@Cliente VARCHAR(12), @TipoContacto INT)

RETURNS TABLE

RETURN

SELECT email = (select ISNULL(email, '') + ';' from linhascontactoentidades lce where lce.entidade = @cliente AND tipoContacto = CASE WHEN @TipoContacto = 1 THEN 'GERENCIA' ELSE 'RESP ADMINISTRA' END FOR XML PATH(''))

Since it doesn't have TYPE nor .value it's much faster and works as well.

Thanks,

Pedro

If you need to work better, try working less...

mickyT SSChampion Points: 10360 More actions · Answer 3

No problem,

I'll have to remember that 🙂 I don't use FOR XML much, but it is useful occasionally