💎SQL Reloaded: Cursors and Lists

    I found cursors really useful when the set-based logic provided by a query make it difficult to solve special types of problems (e.g. concatenate in a list the values returned by a select, multiple updates/insert/deletions based on the success or failure of previous logic). Cursors are relatively easy to write though lot of code is redundant from one solution to another. Therefore, I prefer to have the simplest code and then modify it according to new requirements.   

   Here's a simple example of a function that returns a list of emails:

--creating the sample table
CREATE TABLE EmailAddresses(ID int, Email varchar(50))

--insert test records
INSERT EmailAddresses
VALUES (1, 'John.Travolta@star.com')
INSERT EmailAddresses
VALUES (2, 'Robert.DeNiro@star.com')
INSERT EmailAddresses
VALUES (3, 'MegRyan@star.com')
INSERT EmailAddresses
VALUES (4, 'Helen.Hunt@star.com')
INSERT EmailAddresses
VALUES (5, 'Jodie.Foster@star.com')

-- creating the function 
CREATE FUNCTION dbo.GetEmails()
RETURNS varchar(1000)
/*
Purpose: returns a list of Emails
Parameters:
Notes:
Sample: SELECT dbo.GetEmails() AS ListEmails
*/
AS
BEGIN
   DECLARE @Email varchar(50)
   DECLARE @Emails varchar(1000)

   SET @Emails = ''

   -- Create Emails Cursor
   DECLARE Emails CURSOR FOR
   SELECT Email
   FROM EmailAddresses
   ORDER BY Email

   OPEN Emails -- Open Emails Cursor

   --fetch first set of records from Emails Cursor
   FETCH NEXT FROM Emails
   INTO @Email

   WHILE @@FETCH_STATUS = 0 --if the fatch was successful
   BEGIN
      SET @Emails = @Emails + @Email + ','

      --fetch next set of records from Emails Cursor
      FETCH NEXT FROM Emails
      INTO @Email
   END

   CLOSE Emails -- close Emails cursor
   DEALLOCATE Emails --deallocate Emails cursor

   --remove the extra comma
   IF Len(@Email)>0
      SET @Emails = Left(@Emails, Len(@Emails)-1)

   RETURN @Emails
END

--testing the function
SELECT dbo.GetEmails()

Notes:
The code was tested on SQL Server 2000 till 2017.
The logic from dbo.GetList function can use as source any other table as long the length of the target column is less than 50 characters as the @Result was defined as varchar(50).

     Exploring the above idea, what if the content of the query is not known until runtime and all is known is that only one column of data is returned? This could be achieved with the help of a table data type:

--creating the source table
CREATE TABLE Countries(ID int, Country varchar(50))

--insert test records
INSERT Countries
VALUES (1, 'US')
INSERT Countries
VALUES (2, 'UK')
INSERT Countries
VALUES (3, 'Germany')
INSERT Countries
VALUES (4, 'Spain')
INSERT Countries
VALUES (5, 'France')

-- creating the function 
CREATE FUNCTION dbo.GetList(
@Query varchar(250))
RETURNS varchar(1000)
/*
Purpose: returns a list of List
Parameters:
Notes:
Sample: SELECT dbo.GetList('SELECT Country FROM Countries ORDER BY Country') AS List
*/

AS
BEGIN
   DECLARE @Result varchar(50)
   DECLARE @List varchar(1000)
   DECLARE @Temp TABLE (Val varchar(10))

   SET @List = ''

   --insert in a table data type the results returned by query
   INSERT @Temp
   EXEC (@Query)

   -- Create List Cursor
   DECLARE List CURSOR FOR
   SELECT Val
   FROM @Temp

   OPEN List -- Open List Cursor

   --fetch first set of records from List Cursor
   FETCH NEXT FROM List
   INTO @Result

   WHILE @@FETCH_STATUS = 0 --if the fatch was successful
   BEGIN
      SET @List = @List + @Result + ','

      --fetch next set of records from List Cursor
      FETCH NEXT FROM List
      INTO @Result
   END

   CLOSE List -- close List cursor
   DEALLOCATE List --deallocate List cursor

   --remove the extra comma
   IF Len(@Result)>0
      SET @List = Left(@List, Len(@List)-1)

   RETURN @List
END

--testing the function 
SELECT dbo.GetList('SELECT Country FROM Countries ORDER BY Country') AS List

Result: List --------------------------
France,Germany,Spain,UK,US (1 row(s) affected)   

   Instead of using a query one can use a stored procedure as well. Here's the stored procedure and the example that calls it:

--creating the stored procedure

CREATE PROCEDURE dbo.pListCountries
AS
SELECT Country
FROM Countries
ORDER BY Country

--testing the function

SELECT dbo.GetList('dbo.pListCountries') AS List

Result: List
--------------------------
France,Germany,Spain,UK,US (1 row(s) affected)  

Notes:
As it seems the above code stopped working between the next editions of SQL Server.