October 14, 2010 at 4:23 am
Hi there,
I'm having difficulties with the implementation of an existing (and working like a charm) UDF.
The existing UDF is a recursive function based on the fact that given a input value, i lookup in a table and find a single occurrence. Well, the single-ness has been proved wrong. There are items that have multiple occurrence in the table, and now i have to concatenate all returned values.
I.E.
given the table:
1.1.1.1; dummydummy.uk
dummydummy.uk; dummy.uk
1.1.1.1; dummydummier.uk
dummydummier.uk; dummier.uk
as is:
function('1.1.1.1') returns "dummy.uk"
(1.1.1.1 --> dummydummy.uk --> dummy.uk)
to be:
function('1.1.1.1') returns "dummy.uk, dummier.uk"
so far i managed to get NULL 🙂
October 14, 2010 at 4:27 am
DECLARE @result AS VARCHAR(1000)
SELECT @result = COALESCE(@result + ', ') + dummy_column
FROM dbo.your_table
WHERE where_column = @input
RETURN @result
October 14, 2010 at 4:30 am
concatenate all returned values.
Doesn't look to me like a great idea. Consider changing the function from scalar to ITVF.
Anyway, you can use something like this:
SET @concatenatedString =
STUFF (
(
SELECT ',' + SomeColumn AS [text()]
FROM SomeTable
WHERE OtherColumn = @SomeFilter
FOR XML PATH('')
)
, 1, 1, SPACE(0))
-- Gianluca Sartori
October 14, 2010 at 4:59 am
Uhmm...not sure i made myself clear 🙂
Anyway: this is a simplified version of the working UDF
create function [dbo].[alias2dnsname]
(
@alias nvarchar(50)
)
returns nvarchar(50)
as
BEGIN
declare @tmpresult nvarchar(50)
declare @result nvarchar(50)
if exists(select * from dnszone as D where
d.alias = @alias)
BEGIN
select @tmpresult=dnsname from dnszone as D where
D.alias = @alias;
select @result=dbo.alias2dnsname(@tmpresult);
return @result;
END;
ELSE BEGIN
return @alias;
END;
return @result;
END;
how do i take into account multiple occurrences of @alias in D? I tried a cursor, but to no avail
create function [dbo].[alias2dnsname_c]
(
@alias nvarchar(50)
)
returns nvarchar(50)
as
BEGIN
declare @tmpresult nvarchar(50)
declare @result nvarchar(50)
if exists(select * from dnszone as D where
d.alias = @alias)
BEGIN
declare cursor....
select dnsname from dnszone as D where D.alias = @alias;
fetch into @tmpresult
while
begin
select @runresult=dbo.alias2dnsname_C(@tmpresult);
set @result=@result+','+@runresult;
fetch into @tmpresult
end;
return @result;
END;
ELSE BEGIN
return @alias;
END;
return @result;
END;
this one returns a NULL...so i guess i'm doing something wrong there 😛
October 14, 2010 at 5:01 am
davrose (10/14/2010)
Uhmm...not sure i made myself clear 🙂
Nope - you made yourself perfectly clear. The problem is that you have come to the conclusion the best solution uses a cursor, but it doesn't. Both the snippets posted would need to be incorporated into your function replacing what you have written.
October 14, 2010 at 5:31 am
hallidayd (10/14/2010) you have come to the conclusion the best solution uses a cursor, but it doesn't
I see!! 😉
Thanks, i'll do some test with the posted code! 🙂
October 14, 2010 at 7:21 am
Sorry guys... can't get the hang of it.
hallidayd i get an error, can you explain me how your snippet works? AFAIK coalesce need more than just one argument
I'm now giving a try to TVF
Any help is welcome
October 14, 2010 at 7:30 am
I think he meant COALESCE(@result, '')
-- Gianluca Sartori
October 14, 2010 at 7:34 am
Apols - my bad.
COALESCE(@result + ', ', '') + dummy_column
October 14, 2010 at 8:07 am
This function
BEGIN
--declare @tmpresult nvarchar(50)
declare @result nvarchar(50)
if exists(select * from test_IP as D where
d.vip = @alias)
BEGIN
select @result=coalesce(dbo.test(@result)+', ','')+dnsname from test_IP as D where
D.vip = @alias;
return @result;
END;
ELSE BEGIN
return @alias;
END;
return @result;
END;
returns
dummy.uk, dummydummier.uk
i need
dummy.uk, dummier.uk
gianluca, hints on TVF?
October 14, 2010 at 8:17 am
That will be due to the dbo.Test, whatever that is.
October 14, 2010 at 8:22 am
mind that:
there might be more than 2 occurrences
there might be more that 2 iteration
i.e.
1.1.1.1 dummydummy.uk
dummydummy.uk dummy.uk
1.1.1.1 dummydummier.uk
dummydummier.uk dummier.uk
1.1.1.1 dummydummiest.uk
dummydummiest.uk dummiest.uk
1.1.1.2 2tier.gov
2tier.gov 2tier.uk
2tier.gov 2tier.us
2tier.gov 2tier.jp
1.1.1.3 3rd.uk
3rd.uk 4th.uk
4th.uk 5th.uk
and my goal is a table like this:
1.1.1.1 dummy.uk,dummier.uk,dummiest.uk
1.1.1.2 2tier.uk,2tier.us,2tier.jp
1.1.1.3 5th.uk
October 14, 2010 at 8:23 am
Yes, I had just this second noticed that. I missed the issue with the original formatting.
It should just be a self join though. Hold on...
October 14, 2010 at 8:24 am
hallidayd (10/14/2010)
That will be due to the dbo.Test, whatever that is.
Test is the function
October 14, 2010 at 8:35 am
USE test;
IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.test_IP'))
BEGIN
DROP TABLE dbo.test_IP
END;
CREATE TABLE dbo.test_IP
(
vip VARCHAR(50) COLLATE Latin1_General_CI_AS NOT NULL
, dnsname VARCHAR(50) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY];
INSERT INTO dbo.test_IP
(
vip
, dnsname
)
SELECT '1.1.1.1', 'dummydummy.uk'
UNION ALL SELECT 'dummydummy.uk', 'dummy.uk'
UNION ALL SELECT '1.1.1.1', 'dummydummier.uk'
UNION ALL SELECT 'dummydummier.uk', 'dummier.uk';
IF NOT EXISTS (SELECT NULL FROM sys.objects WHERE type_desc = 'SQL_SCALAR_FUNCTION' AND object_id= OBJECT_ID('dbo.concatenate_domain')) BEGIN
EXEC('
CREATE FUNCTION dbo.concatenate_domain()
RETURNS INT
AS
BEGIN
RETURN NULL
END'
)
END
GO
ALTER FUNCTION dbo.concatenate_domain
(
@alias VARCHAR(50)
)
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @result NVARCHAR(1000)
SELECT @result = COALESCE(@result+', ', '') + domain.dnsname
FROM dbo.test_IP AS IP
INNER JOIN
dbo.test_IP AS domain
ON domain.vip = IP.dnsname
WHERE IP.vip = @alias;
RETURN COALESCE(@result, @alias)
END;
GO
SELECT IP.vip
, dnsname_concatenated = dbo.concatenate_domain(vip)
FROM dbo.test_IP AS IP
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply