• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 210
  • Last Modified:

Stringing results together

I have a function (SQL Server) I use to return values - see below


CREATE FUNCTION [dbo].[GetRelatedRecords] (@id bigint)
returns varchar(250)
as
BEGIN
declare @results varchar(250)
SELECT @results = 'Related Record: ' + R.recordid
FROM RECORDS R
left join LINKS L
      ON R.id = L.LinkID
WHERE L.id = @id
RETURN @results
END


Assuming the value or R.Recordid = 'XYZ' then this will return 'Related Record: XYZ' which is great, but there are sometimes more than one match for L.id = @id and I'd like them all (rather than the last one which is what is currently returned.

So if the results were XYZ and ABC I need to return ''Related Records: XYZ|Related Records: ABC'
0
QPR
Asked:
QPR
1 Solution
 
MMTadminCommented:
Return a table?

Create FUNCTION SalesByStore(@storid varchar(30))
RETURNS TABLE
AS
RETURN (SELECT title, qty
        FROM dbo.sales s, dbo.titles t
        WHERE s.stor_id = @storid AND t.title_id = s.title_id)
GO

If you really want to return a string (strange) then insert into a table variable loop through it and return the string
0
 
QPRAuthor Commented:
This function will be part of a larger select which is why I need the value(s) returned as a single column - how would I loop through the table var to create the string? That would put me in the same situation as my initial question
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:

CREATE FUNCTION [dbo].[GetRelatedRecords] (@id bigint)
returns varchar(250)
as
BEGIN
declare @results varchar(250)
SELECT @results = coalesce(@results + '|' , '' ) + 'Related Record: ' + R.recordid
FROM RECORDS R
left join LINKS L
      ON R.id = L.LinkID
WHERE L.id = @id
RETURN @results
END

Open in new window

0
 
David ToddSenior DBACommented:
Hi QPR,

The functionality angelIII used is known as the Quirky Update. (You can do this in an update as well.

Sort answer is that the @results = ... expression is evaluated for each row and produces a delimited string with the column results.

HTH
  David
0
 
QPRAuthor Commented:
Excellent (as always)
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now