I have a function that usually returns one value. However, when there are multiple rows that it could return, it returns a comma-delimited string of all of the values. I'm new to using functions, so I'm wondering how it does this? Is it looping through? Doesn't the SELECT statement only run once? Here is my code:
CREATE FUNCTION dbo.list_of_cities (@fruit VARCHAR(10))
RETURNS VARCHAR(1000)
AS
BEGIN
DECLARE @output VARCHAR(1000)
SELECT @output = ISNULL(@output + ', ', '') + stores_table.city
FROM fruits_table INNER JOIN stores_table
ON fruits_table.store = stores_table.store
WHERE fruit = @fruit
RETURN @output
END
Basically I want this function to return a list of all the cities that a fruit is sold in. The fruits_table has two columns: fruit and store. The stores_table has two columns: store and city. Most of the time, this will return only one city, but sometimes a fruit is sold in multiple stores, meaning this would return multiple rows. How exactly is this function concatenating the rows into a comma-delimited string? The function works fine, but I'm just curious why/how it's doing this.
The select statement runs only once, but because you have a calculated definition in the query, it evaluates it for each row. As it assigns on each row too, the value of @output changes as it does so, and you get the complete comma separate list.