How can I merge this query result set into a variable to print out later?

Question

so I have this currently

SET @title = (SELECT title FROM fnBorrowerBooks(@name, 'G3'))
SET @dateBorrowed = (SELECT DateBorrowed FROM fnBorrowerBooks(@name, 'G3'))
SET @dateReturned = (SELECT DateReturned FROM fnBorrowerBooks(@name, 'G3'))

but basically what I would need is something like

SET @variable 1 = (SELECT * FROM fnBorrowerBooks(@name, @Genre)

But this returns 3 columns and CAN return multiple ROWS which obviously won't work because you can't assign multiple values, I need to print how many results I get so if I get 3 different results I need to print all 3 as in 1 string if that makes sense so

PRINT CONCAT(@variable1, ' ble ble ble') should return me multiple lines depending on how many results there are, and I was unable to find a way on how to achieve this.

How will this help me? It will count the amount of rows but i actually have to use print ' ' and print all the values concatenated. — SiwyJ, May 22 '18 at 12:38
https://stackoverflow.com/questions/1943892/sql-server-print-select-print-a-select-query-result — EzLo, May 22 '18 at 13:12

Ross Bush · Answer 1 · 2018-05-22T13:08:37.650

1

If you only need the titles then you can concatenate within your query:

DECLARE @Title NVARCHAR(3000) 

SELECT 
    @Title = COALESCE(@Title + ', ', '') + title
FROM 
    dbo.fnBorrowerBooks(@name, @genre)


SELECT @title

edited May 22 '18 at 13:08

answered May 22 '18 at 13:01

Ross Bush

14,648
2
32
55

score 0 · Answer 2 · answered May 22 '18 at 12:55

You could try using a temp table and a loop, like this:

CREATE TABLE #t
(tKey int  IDENTITY(1,1) PRIMARY KEY,
 Column1 varchar(100),
 Column2 varchar(100),
 ...)

INSERT INTO #t
SELECT * --the columns you select here need to be in the column list above
FROM fnBorrowerBooks(@name, @Genre)

DECLARE @Count int
DECLARE @MyString varchar(max)

SET @Count = 1
SET @MyString = ''

WHILE @Count <= (SELECT MAX(Key) FROM #t)
BEGIN
    SET @MyString += (SELECT Column1 + ', ' + Column2 + ... FROM #t WHERE tKey = @Count)
    SET @Count += 1
END

PRINT(@MyString)

How can I merge this query result set into a variable to print out later?

2 Answers2