While programming with sql we usually come across a situation where we want to pass data to stored procedure where the data is actually a string concatenation of different values and the stored procedure would construct a table out of this string to perform relational operations. Let’s look at one such scenario.
Scenario:
Say you have the following tables
CREATE TABLE [dbo].[Product](
ProductID INT IDENTITY(1,1) PRIMARY KEY
, Name VARCHAR(200)
, Description VARCHAR(MAX)
)
CREATE TABLE [dbo].[Part](
PartID INT IDENTITY(1,1) PRIMARY KEY
, Name VARCHAR(30)
)
CREATE TABLE [dbo].[ProductPart](
ProductID INT FOREIGN KEY REFERENCES Product(ProductID)
, PartID INT FOREIGN KEY REFERENCES Part(PartID)
)
Let’s populate these tables with test data
INSERT INTO Product(Name, Description)
VALUES('Car', 'it will take you to work')
INSERT INTO Part(Name)
VALUES('Wheel'),('Door'), ('Boot'), ('Monitor')
These tables will now have the following data
Let us suppose you want to store the data into ProductPart table. You want to store the information where you want to say a car has wheels, Doors and a Boot.
Traditionally you will have to write a stored procedure where it will take partID and ProductID as parameters and saves it into the ProductPart table one at a time.
It would look like this
CREATE PROCEDURE [dbo].[ProductPartSave]
(
@ProductID INT
, @PartID INT
)
AS
INSERT INTO [ProductPart]
SELECT @ProductID, @PartID
It will work fine. Except that you will have to make three calls to the database in our case.
There is one more way. You can create a stored procedure that will take two parameters. The first parameter would be the @ProductID and the second @PartIDs.
Definition of our new stored procedure is
CREATE PROCEDURE [dbo].[ProductMultiplePartsSave]
(
@ProductID INT
, @PartIDs VARCHAR(MAX)
)
AS
DECLARE @Parts TABLE(PartID INT)
INSERT INTO @Parts(PartID)
EXEC [GetDynamicTable] @DataList = @PartIDs, @ColumnSeperator = ',', @RowSeperator = '|', @NumberOfColumns = '1'
INSERT INTO [ProductPart](ProductID, PartID)
SELECT @ProductID
, PartID
FROM @Parts
As you can see, we have a used a stored procedure called [dbo].[GetDynamicTable] to get a table from string.
The code for this sp is
CREATE PROCEDURE [dbo].[GetDynamicTable]
(
@DataList AS VARCHAR(MAX),
@ColumnSeperator AS CHAR(1),
@RowSeperator AS CHAR(1),
@NumberOfColumns INT
)
AS
DECLARE @DynamicTableSQL NVARCHAR(MAX)
DECLARE @ColumnCount INT
DECLARE @Row NVARCHAR(MAX)
SET @DynamicTableSQL = 'DECLARE @DynamicTable TABLE('
SET @ColumnCount = 1
WHILE @ColumnCount <= @NumberOfColumns
BEGIN
SET @DynamicTableSQL = @DynamicTableSQL + 'Column' + CAST(@ColumnCount AS VARCHAR(10))+ ' VARCHAR(MAX) ,'
SET @ColumnCount = @ColumnCount + 1
END
SET @DynamicTableSQL = SUBSTRING(@DynamicTableSQL, 0, LEN(@DynamicTableSQL))
SET @DynamicTableSQL = @DynamicTableSQL + ') '
SET @DataList = REPLACE(@DataList, '''', '''''')
SET @DynamicTableSQL = @DynamicTableSQL + ' INSERT INTO @DynamicTable '
SELECT @DataList = @DataList + @RowSeperator
DECLARE @PosA INT, @PosB INT, @PosC INT
SELECT @PosA = 1
SELECT @PosC = CHARINDEX(@RowSeperator, @DataList, @PosA)
SET @Row = SUBSTRING(@DataList, @PosA, @PosC - @PosA)
IF (LEN(@Row) > 0)
BEGIN
SET @Row = REPLACE( @Row, @ColumnSeperator, ''',''')
SET @DynamicTableSQL = @DynamicTableSQL + 'SELECT ''' + @Row + ''' '
END
SELECT @PosA = @PosC + 1
SELECT @PosC = CHARINDEX(@RowSeperator, @DataList, @PosA)
WHILE @PosC > 0
BEGIN
SET @Row = SUBSTRING(@DataList, @PosA, @PosC - @PosA)
IF (LEN(@Row) > 0)
BEGIN
SET @DynamicTableSQL = @DynamicTableSQL + ' UNION ALL '
SET @Row = REPLACE( @Row, @ColumnSeperator, ''',''')
SET @DynamicTableSQL = @DynamicTableSQL + 'SELECT ''' + @Row + ''' '
END
SELECT @PosA = @PosC + 1
SELECT @PosC = CHARINDEX(@RowSeperator, @DataList, @PosA)
END
SET @DynamicTableSQL = @DynamicTableSQL + ' SELECT * FROM @DynamicTable '
EXEC SP_EXECUTESQL @DynamicTableSQL
The above stored procedure takes four parameters
1. @DataList – a string containing the concatenated data
2. @ColumnSeperator – a character which is the column delimiter
3. @RowSeperator – a character which is the row delimiter
4. @NumberOfColumns – number of columns the dynamic table should have
Let’s first see how we can insert values into ProductPart table using our new stored procedures.
As you can see, we inserted multiple rows into ProductPart table with just one call to the [ProductMultiplePartsSave] stored procedure.
[dbo].[GetDynamicTable] is very powerful in terms of creating temp tables on the fly with just change of parameters. Let’s look at a few different uses of this sp:
In the following image you can see how it can be used to get a two column table
In the following image you can see it returning a table with 5 columns
I hope this article was informative and please don’t hesitate to comment or ask questions.
