Fetch Data from Multiple Records

You can use function to fetch data from multiple records into one field. If for example we have table users and hobbies.


user_id
user_name
address
1 John Walker Bangladesh
2 Frank McLane Nepal
3 Leon Kennedy USA


user_id
hobby
1Reading
1 Sleeping
1 Walking
2 Swimming
3 Help People

If we're asked to display the user's name along with his/her hobbies, first we create a function to fetch the hobbies of a user into a single column. The function is as follows


CREATE FUNCTION [dbo].[get_hobbies]

(
@usr_id int
)
RETURNS varchar(255)
AS
BEGIN
DECLARE @hobbies varchar(255)
DECLARE @temp varchar(255)

SET @hobbies = ''
SET @temp = ''

DECLARE HbCur CURSOR FOR SELECT hobby FROM users inner join hobbies on users.[user_id] = hobbies.[user_id] WHERE users.[user_id]=@usr_id

OPEN HbCur
FETCH next FROM HbCur INTO @temp
WHILE @@Fetch_Status = 0
BEGIN
SET @hobbies = @hobbies + @temp + ', '
FETCH next FROM HbCur INTO @temp
END


CLOSE HbCur
DEALLOCATE HbCur

--remove the last unnecessary comma
SET @hobbies = substring(@hobbies, 1, len(@hobbies)-1)

RETURN @hobbies
END


The function is quite self-explanatory. The body of the function starts after the keyword BEGIN and end at the END keyword. It takes the user_id as a parameter which will be used to select all hobbies of that user. Here, two variables are declared, @hobbies and @temp. These two variables are used to store the result of the hobbies queried. Next, we declare a CURSOR that will hold the result hobbies from the query, and then can be iterated to get each hobby. Next, there is a loop which will fetch every single hobby to the @temp variable which will then be appended to @hobbies variable that will hold all of the hobbies separated by comma. Last, the function returns all of the hobbies.


To test the function you can run the following sql


SELECT [dbo].[get_hobbies] (1)


This is the output when I run the query.



0 comments:

top