looping in 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)

looping in function

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2011-10-27 : 02:24:21

I created a function to get some information from a table and return it as a string
the issue is it is now only returning 1 row - if there is more I want it to return all rows just separated by a -

How would I change this

ALTER function [dbo].[getplinfo]
(
@participantid int
)
returns nvarchar(450)
as
begin
declare @retval nvarchar(450)

select @retval =nameoforganization + '-' + pc.city + '-' + isnull(convert(varchar, datebeganposition, 103),'')+ '-' + isnull(convert(varchar, dateendedposition, 103),'') from pl left join city pc on pc.id=pl.workcityid where pl.participantid=@participantid

return @retval
end
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-27 : 02:56:58

[code]
ALTER function [dbo].[getplinfo]
(
@participantid int
)
returns nvarchar(450)
as
begin
declare @retval nvarchar(450)

select @retval =coalesce(@retval,'') + nameoforganization + '-' + pc.city + '-' + isnull(convert(varchar, datebeganposition, 103),'')+ '-' + isnull(convert(varchar, dateendedposition, 103),'') from pl left join city pc on pc.id=pl.workcityid where pl.participantid=@participantid

return @retval
end
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources