HOW TO GENERATE COMMA SEPERATED STRING USING STUFF FUNCTION IN TRANSACT-SQL
Tags: transactsql, sql, sqlserver
Published: 2023-05-24 06:23:49

{% embed https://youtu.be/Jrs4sjCCeUw %}

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](
 [CategoryID] [int] IDENTITY(1,1) NOT NULL,
 [CategoryName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ProductCategories] PRIMARY KEY CLUSTERED 
(
 [CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) 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](
 [ProductID] [bigint] IDENTITY(1,1) NOT NULL,
 [ProductName] [nvarchar](70) NOT NULL,
 [UnitPrice] [decimal](18, 2) NOT NULL,
 [CategoryID] [int] NOT NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
 [ProductID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[ProductCategories] ON 
GO
INSERT [dbo].[ProductCategories] ([CategoryID], [CategoryName]) VALUES (1, N'Sport Equipment')
GO
INSERT [dbo].[ProductCategories] ([CategoryID], [CategoryName]) VALUES (2, N'Music Equipment')
GO
SET IDENTITY_INSERT [dbo].[ProductCategories] OFF
GO
SET IDENTITY_INSERT [dbo].[Products] ON 
GO
INSERT [dbo].[Products] ([ProductID], [ProductName], [UnitPrice], [CategoryID]) VALUES (2, N'FIFA U-17 2022 World Cup Match Ball', CAST(456.22 AS Decimal(18, 2)), 1)
GO
INSERT [dbo].[Products] ([ProductID], [ProductName], [UnitPrice], [CategoryID]) VALUES (3, N'YAMAHA PSR-570 Keyboard', CAST(3456.57 AS Decimal(18, 2)), 2)
GO
INSERT [dbo].[Products] ([ProductID], [ProductName], [UnitPrice], [CategoryID]) VALUES (4, N'Shure Microphone', CAST(872.12 AS Decimal(18, 2)), 2)
GO
INSERT [dbo].[Products] ([ProductID], [ProductName], [UnitPrice], [CategoryID]) 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] ([CategoryID])
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