Scalar Function Code in SQL

  • 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

  • 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;

  • 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