Problems with Function? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problems with Function?

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-08-06 : 10:43:01

I have a function which reads a link server table and returns all rows back to me but displays as one row based on it's where clause. See Below:

ALTER function [dbo].[fn_ReturnBranches]
(
@mid varchar(3)
)
returns varchar(8000)
as
begin
declare @v varchar(8000)
select @v = ''
select @v = @v + Orb.mib_symbol + ', '
from servername.dbname.dbo.company Orb
where (Orb.home_office_symbol = @mid and Orb.status = 'Active')
select @v = left(@v, len(@v)-1)
return @v
end

Lets say that we pass '8' to this function, I know that the select statement should return 3 records thus showing something like this being returned from the function '8, 8L, 8Z'

What I am getting back from the function is '8Z' only.
I expect to see '8, 8L, 8Z'
Am I doing somethign wrong in this function?

Hope this makes sense.
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-06 : 10:50:14

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254
for how to do this in SQL Server 2005.

N 56°04'39.26"
E 12°55'05.63"

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2009-08-06 : 10:55:26

Great, thanks Peso!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources