September 1, 2009 at 5:09 pm
Hi,
I need help obtaining information from a table and returning it in one string variable.
The part I am having trouble with is in Bold below. It searches through all the results of the @MainSitesTable and puts it into one string variable. I have written it in Access language but need to convert to operate in SQL.
----------------------------------------
CREATE FUNCTION fn_GetFarEndSitesList(@FarEndProcessID int)
RETURNS nvarchar(1000)
AS
BEGIN
-- Declare the return variable here
DECLARE @FarEndList nvarchar(1000), @MainSitesTable Table (Site nvarchar(200))
-- Add the T-SQL statements to compute the return value here
SET @MainSitesTable =
SELECT (dbo.tblSites.SiteNumber + ' '+dbo.tblSites.SiteName) as Site
FROM dbo.tblFarEndConnections INNER JOIN
dbo.tblProcesses ON dbo.tblFarEndConnections.MainProcessID = dbo.tblProcesses.ProcessID INNER JOIN
dbo.tblSites ON dbo.tblProcesses.SiteID = dbo.tblSites.SiteID
WHERE (dbo.tblFarEndConnections.FarEndProcessID = @FarEndProcessID)
SET @FarEndList =''
@MainSitesTable.MoveFirst
DO UNTIL @MainSitesTable.EOF
SET @FarEndList = @FarEndList + @MainSitesTable.Site +'and'
@MainSitesTable.MoveNext
LOOP
RETURN @FarEndList
END
I would appreciate any help on how to write this in SQL Code.
Thanks
September 1, 2009 at 7:56 pm
Give this code a spin. It is untested as you did not provide the DDL for the tables or any sample data. To see how you really should post this information please read the first article I reference below in my signature block.
declare @FarEndList nvarchar(1000);
WITH Sites as (
SELECT
sit.SiteName
FROM
dbo.tblFarEndConnections fec
INNER JOIN dbo.tblProcesses prc
ON fec.MainProcessID = prc.ProcessID
INNER JOIN dbo.tblSites sit
ON prc.SiteID = sit.SiteID
WHERE
(fec.FarEndProcessID = @FarEndProcessID)
)
select
@FarEndList = replace((select ',' + SiteName from Sites for xml path ('')),',','');
select @FarEndList;
September 2, 2009 at 1:46 am
Hi,
Thanks for the reply. It worked. Much appreciated.
Regards
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply