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.
No comments:
Post a Comment