• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 217
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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