rowset concatenation problem: view vs. table

[Ned Balzer]

I found some code that will concatenate some values from a table into
a single string, and it works but only under certain conditions -- I
am stumped as to why.

The code I found: (at http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx):
USE pubs
GO

CREATE FUNCTION dbo.ConcatAuthors(@State CHAR(2))
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @Output VARCHAR(8000)
SET @Output = ''

SELECT @Output = CASE @Output
WHEN '' THEN au_lname
ELSE @Output + ', ' + au_lname
END
FROM Authors
WHERE State = @State
ORDER BY au_lname

RETURN @Output
END
GO

To use the UDF:

SELECT DISTINCT State, dbo.ConcatAuthors(State)
FROM Authors
ORDER BY State

I tried to modify this code to produce a list of peripherals attached
to a particular desktop computer:

CREATE function dbo.f_attachedPeripherals(@computerID int)
returns varchar(8000)
as
begin
declare @output varchar(8000)
select @output = ''
select @output = case @output
when '' then xtypevc
else @output + ', ' + xtypevc
end
from v_peripherals_3
where cdevid = @computerID
order by cdevid, xtypevc

return @output
End

But it doesn't work, evidently because the select statement I used is
based on a view instead of a table. The function does not concatenate
additional values past the first one. Here is the view definition:


CREATE view dbo.v_peripherals_3 as
select d.deviceID as peripheralID, d.devicename as name, cp.deviceID
as cpdevID, c.deviceID as cdevID, d.deviceID as ddevID,
d.devicetypeID,
Case d.devicetypeID
when 5 then 'Printer'
when 7 then 'Scanner'
when 6 then 'Monitor'
End as dtypevc,
Case d.devicetypeid
when 5 then pr.type
when 7 then s.type
when 6 then m.type
else Null
End as xtypevc,
d.status, d.notes
from dbo.computer_peripheral cp
inner join dbo.devices d
on cp.peripheralID = d.deviceID
inner join dbo.computers c
on cp.deviceID = c.deviceID
left outer join dbo.printers pr
on cp.peripheralID = pr.peripheralID
left outer join dbo.monitors m
on cp.peripheralID = m.peripheralID
left outer join dbo.scanners s
on cp.peripheralID = s.peripheralID



When I created a table from the view:
select * into table_v_peripherals_3 from v_peripherals_3

and then modified the UDF to reference the table instead of the view,
it works as I expected it to -- it concatenates all the values. Here
is the modified UDF:

CREATE function dbo.f_attachedPeripherals(@computerID int)
returns varchar(8000)
as
begin
declare @output varchar(8000)
select @output = ''
select @output = case @output
when '' then xtypevc
else @output + ', ' + xtypevc
end
from table_v_peripherals_3
where cdevid = @computerID
order by cdevid, xtypevc

return @output
End


Can anyone explain why the function works when it references the table
but not the view? I'd hate to have to create a temp table each time
the function is called

In case it makes a difference, this is SQL2000.

Thanks.

-- Ned Balzer
(e-mail address removed)

 

[Ned Balzer]

My apologies, I meant to post this to a SQL group. I have reposted to
a SQL group -- please ignore this post here.

-- Ned