HOW TO GENERATE COMMA SEPERATED STRING USING STUFF FUNCTION IN TRANSACT-SQL

Concepts Covered In This Video Include:-

1. How To Connect To SQL SERVER using management studio. 2.How to create a new database using management studio. 3.How to create tables using management studio 4.How to create relationship between 2 tables using management studio 5.How to insert data into tables using management studio 6.How to create new stored procedures 7.How to use STUFF Transact-SQL function to generate comma separated strings 8.How to execute stored procedures from management studio 9.How to pass new set of parameters to a recently executed stored procedure in management studio

TRANSACT SQL QUERY FOR THE ENTIRE EXERCISE CAN BE FOUND BELOW

USE [master]
GO
/****** Object: Database [ProductCatalogueDB] Script Date: 5/24/2023 6:40:17 AM ******/
CREATE DATABASE [ProductCatalogueDB]

GO

USE [ProductCatalogueDB]
GO
/****** Object: Table [dbo].[ProductCategories] Script Date: 5/24/2023 6:40:17 AM ***/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].ProductCategories ON [PRIMARY]
GO
/*** Object: Table [dbo].[Products] Script Date: 5/24/2023 6:40:17 AM ***/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].Products ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[ProductCategories] ON
GO
INSERT [dbo].ProductCategories VALUES (1, N'Sport Equipment')
GO
INSERT [dbo].ProductCategories VALUES (2, N'Music Equipment')
GO
SET IDENTITY_INSERT [dbo].[ProductCategories] OFF
GO
SET IDENTITY_INSERT [dbo].[Products] ON
GO
INSERT [dbo].Products VALUES (2, N'FIFA U-17 2022 World Cup Match Ball', CAST(456.22 AS Decimal(18, 2)), 1)
GO
INSERT [dbo].Products VALUES (3, N'YAMAHA PSR-570 Keyboard', CAST(3456.57 AS Decimal(18, 2)), 2)
GO
INSERT [dbo].Products VALUES (4, N'Shure Microphone', CAST(872.12 AS Decimal(18, 2)), 2)
GO
INSERT [dbo].Products VALUES (5, N'FIBA 2021 Men''s World Cup Basketball', CAST(762.12 AS Decimal(18, 2)), 1)
GO
SET IDENTITY_INSERT [dbo].[Products] OFF
GO
ALTER TABLE [dbo].[Products] WITH CHECK ADD CONSTRAINT [FK_Products_ProductCategories] FOREIGN KEY([CategoryID])
REFERENCES [dbo].ProductCategories
GO
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_ProductCategories]
GO
/*** Object: StoredProcedure [dbo].[CommaSeparatedListsOfProductsByCategoryID] Script Date: 5/24/2023 6:40:18 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CommaSeparatedListsOfProductsByCategoryID]
@CategoryID int
AS
SELECT Products = STUFF((
SELECT ',' + ProductName
FROM Products
WHERE (CategoryID = @CategoryID)
FOR XML PATH('')
), 1, 1, '')
GO
USE [master]
GO
ALTER DATABASE [ProductCatalogueDB] SET READ_WRITE
GO

Comments

[Olesia Dudareva]

Actually, STUFF() doesn't generate anything. It just replaces the first comma in the string with the emptiness. The string is generated by FOR XML.

Anyway, this approach is very good and I use it quite often. But SQL Server version 2017 and higher has another very good function STRING_AGG()
Just for information, because your version is 2016.

For instance your example will be looking like the code below:

SELECT STRING_AGG(ProductName,',') as list_of_products
FROM Products
WHERE (CategoryID = @CategoryID)

This is the result:

list_of_products
Cream,Milk,Yogurt

Thanks @rozhnev for your great tool (not sponsored)! I just have SQL Server V.2014 on my machine and it's difficult to show new functionality using it.